Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trigger question Expand / Collapse
Author
Message
Posted Friday, September 4, 2009 11:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 9:03 AM
Points: 37, Visits: 237
Howdy folks,

I just realized I'm disappointed with sql server 2008's trigger capabilities because I was not quite able to implement one of the developer's request.
I need to create a trigger that updates a DateModifed column for a given set of rows that are being updated by a DML statement. After some internet research, I came up with something like this:

CREATE TRIGGER trg_UpdateDateModified
ON Employees
INSTEAD OF UPDATE
AS
BEGIN
IF UPDATE(FirstName)
UPDATE
E
SET
E.DateModified = GETDATE(),
E.FirstName = I.FirstName
FROM
Employees E, inserted I
WHERE
E.FirstName=I.FirstName
END
GO

I see some huge problems with this:
- The trigger will fire only when FirstName column will be targeted with an update. If I want the trigger to fire for any columne that will be targeted with an update, I'll have to implement conditions within the trigger for every single column part of the table - that's kind of unacceptable;

- Conversely, if I create a generic trigger that fires for any update, without to specify the column in IF UPDATE(column) I will not be able to update the DateModified rows that were targeted by the triggering DML statement in the first place. The result would be that all the DateModified entries in the whole table would be updated;

- Assuming I choose to go down on the path of creating the trigger condition for every single column in the table, the update statement inside trigger is totally inefficient because it will have to use indexes to find out which rows have been updated (it's an update join as you can see) so for no reason on earth I will create 1 index for each column in the table.

- Even so, the trigger will yield improper results when the inside update-join will affect existing rows in the table that WERE not targeted by triggering DML statement. I've seen it with my own eyes in the ensuing tests.

- Assuming that I'm still nuts enough to implement the above solution, I'll have to write triggers like this for every single freaking table and column in the db; the db is supposed to replicate in large lines whatever is in a old mysql db, which as y'all know has one of the few nifty things found in mysql:
`date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

To wrap up things, in Oracle the above state trigger would look absolutely simple and elegant, like this:

CREATE OR REPLACE TRIGGER trg_UpdateDateModified
BEFORE INSERT OR UPDATE
ON Employees
FOR EACH ROW
DECLARE
BEGIN

IF INSERTING THEN
:NEW.DateCreated := SYSDATE;
:NEW.DateModified := SYSDATE;
END IF;

IF UPDATING THEN
:NEW.DateModified := SYSDATE;
END IF;

EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20101, 'Error : '||SQLCODE||' - '||SQLERRM);

END;

So, I dare you to solve this one.
Post #783116
Posted Friday, September 4, 2009 12:04 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Try this:
CREATE TRIGGER trg_UpdateDateModified
ON Employees
FOR UPDATE
AS
UPDATE Employees
SET DateModified = getdate()
FROM Employees
INNER JOIN inserted
on Employees.ID = inserted.ID;

I'm assuming the existence of an ID column in the Employees table. You'll need to modify it to use the correct primary key in the join if ID isn't what you're using.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #783124
Posted Friday, September 4, 2009 12:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 9:03 AM
Points: 37, Visits: 237
I'll try it in a sec, but I couldn't help it to ask, where's that inserted.ID coming from? Yes, I do have a PK in the table, but if the triggering DML statement doesn't involve any PK, how's that going to help?
Post #783127
Posted Friday, September 4, 2009 12:16 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Doesn't matter if the DML has the ID column in it or not. Triggers have two "tables", one called "inserted", one called "deleted". The inserted one is a snapshot of each row that was either inserted or updated. The deleted one is a snapshot of each row that was deleted, or each row that was updated, with the pre-update data. In other words, inserted = after, deleted = before, for updates.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #783129
Posted Friday, September 4, 2009 12:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 9:03 AM
Points: 37, Visits: 237
Ok, that I must be doing something wrong, because remember getting through this variant of the trigger and it doesn't do what it supposed to do.
Here is the table:

CREATE TABLE [dbo].[Employees](
[DateCreated] [datetime] NULL,
[DateModified] [datetime] NULL,
[Id] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[DeptIdt] [int] NOT NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DeptIdt])
REFERENCES [dbo].[Departments] ([Id])
GO

ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]
GO


The entire data set is really simple, for exemplification purposes:

DateCreated DateModified Id FirstName LastName DeptIdt
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 8 John Doe 1
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 9 Marry Doe 2
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 10 John Long Silver 3
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 11 Ted King 2


The trigger is in place and looking like this:

IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_UpdateDateModified]'))
DROP TRIGGER [dbo].[trg_UpdateDateModified]
GO

CREATE TRIGGER [dbo].[trg_UpdateDateModified]
ON [dbo].[Employees]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE
Employees
SET
DateModified = getdate()
FROM
Employees
INNER JOIN inserted
ON Employees.ID = inserted.ID;
END
GO


I run the following queries:

select * from Employees
update Employees SET FirstName = 'Johnny' where Id=8
select * From Employees


and finally, here are the results:
BEFORE update
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 8 John Doe 1
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 9 Marry Doe 2
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 10 John Long Silver 3
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 11 Ted King 2

AFTER update:
2009-09-04 11:23:48.460 2009-09-04 11:28:28.813 8 John Doe 1
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 9 Marry Doe 2
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 10 John Long Silver 3
2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 11 Ted King 2


As you can see, the DateModified is updated, but NOT the FirstName column. Am I missing something here?
Post #783141
Posted Friday, September 4, 2009 12:38 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
That's why my version of the trigger is "For Update", not "Instead of Update". That's one of the key differences.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #783145
Posted Friday, September 4, 2009 12:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 9:03 AM
Points: 37, Visits: 237
Nevermind my previous question, I just realized that I'm using INSTEAD OF when you were using FOR UPDATE.
I corrected the trigger definition, tested it and it seems to work.

Any reasons NOT to have such a trigger on production?
Post #783146
Posted Friday, September 4, 2009 12:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 5, 2013 9:03 AM
Points: 37, Visits: 237
Also a question about this trigger. Is it acting like an AFTER trigger, generating an additional IO for each DML or is it doing the changes in the buffer and applying them after it finished execution?

If it's an after trigger, I'm not gonna recommend to the developer due to the fact it might create problems in the long run - additional IO requests just for a simple update on DateModified.
Post #783157
Posted Friday, September 4, 2009 12:54 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
The main objection to any trigger is that it's hidden from the developers. A more standard way to update that column would be to make sure the update proc includes it in the definition, and then make sure no updates are done to the table except through that proc. Then the code isn't hidden from anyone. Of course, it's only hidden from devs who don't really know their business, but that's an unfortunately high percentage.

The other reason to not use such a trigger is that the whole "DateUpdated" column is, in most cases, close to useless.

If it's being used to determine whether the data should be archived or not, then it's useful. If it's being used as part of an audit trail, it's useless. (Just like the common "UpdatedBy" column is useless.)

Other than those two things, no, such triggers are pretty common, and can be considered a standard practice.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #783158
Posted Friday, September 4, 2009 12:59 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Daniel C (9/4/2009)
Also a question about this trigger. Is it acting like an AFTER trigger, generating an additional IO for each DML or is it doing the changes in the buffer and applying them after it finished execution?

If it's an after trigger, I'm not gonna recommend to the developer due to the fact it might create problems in the long run - additional IO requests just for a simple update on DateModified.


Triggers get incorporated into the transaction that causes them to be fired off. Test it, of course, but it shouldn't cause any significant increase in I/O over the original transaction.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #783163
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse