asked on
Need an update trigger to log last update to table
Is there a quick and easy way to have a datetime column in a table that will catch 'lastupdated' date and time. I can do this for 'inserted'/'created' by having a datetime column with a default value of getdate(), but is there a trick to do the same for updated.
ie
create table1
( test vachar(1),
created_date datetime default getdate(),
update_date datetime,
)
and have 'updated_date' column updated each time an update is made to the table.
I presume I require an update trigger, but how do I populate the updated_date column within the update trigger- any thoughts. Or is there another way.
Please no-one mention timestamp - I know all about that and it doesnt do what it says on the tin
thanks
KD
ie
create table1
( test vachar(1),
created_date datetime default getdate(),
update_date datetime,
)
and have 'updated_date' column updated each time an update is made to the table.
I presume I require an update trigger, but how do I populate the updated_date column within the update trigger- any thoughts. Or is there another way.
Please no-one mention timestamp - I know all about that and it doesnt do what it says on the tin
thanks
KD
DatabasesMicrosoft SQL ServerSQL
Last Comment
dqmq
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
thanks
just out of interest - why include 'deleted' in this solution ?
would the pseudo row 'deleted' even exist in an insert,update trigger ?
just out of interest - why include 'deleted' in this solution ?
would the pseudo row 'deleted' even exist in an insert,update trigger ?
The deleted row is needed for the old value of the "modified" column. The trigger provides for you to explicitly set "modified" to some other value if necessary (e.g. using Query Analyzer) - the (i.modified = d.modified) condition compares the old and the new values of "modified" - if they are different then you explicitly changed "modified" and don't want that to be overwritten in this trigger. I know I haven't said it that clearly but you get the picture, right?
For updates you always get a deleted and an inserted row (a kind of "before" and "after" image), for inserts you only get an inserted row - the d.PK = i.PK join means that the update statement won't affect any rows that are only Inserts - saving a little time in those cases, however you will have to make sure that "modified" has a default of "GetDate()" for this to be set correctly for inserts.
For updates you always get a deleted and an inserted row (a kind of "before" and "after" image), for inserts you only get an inserted row - the d.PK = i.PK join means that the update statement won't affect any rows that are only Inserts - saving a little time in those cases, however you will have to make sure that "modified" has a default of "GetDate()" for this to be set correctly for inserts.
With that solution you will need to enable nested triggers. :>)
The real reason for this:
(i.modified = d.modified)
or (i.modified is null and d.modified is null)
is to prevent infinite recursion after having necessarily enabled it! In other words, since the trigger updates the [modified] column of the triggered table, it will fire again. The second time, you do NOT want it to update the [modified] column once again therefore fire again and again and again.
My curiosity about the proposed solution is this. Why bother to create an insert trigger that, by virtue of joining on the deleted table (which is NULL on insert), is incapapble doing anything?
The real reason for this:
(i.modified = d.modified)
or (i.modified is null and d.modified is null)
is to prevent infinite recursion after having necessarily enabled it! In other words, since the trigger updates the [modified] column of the triggered table, it will fire again. The second time, you do NOT want it to update the [modified] column once again therefore fire again and again and again.
My curiosity about the proposed solution is this. Why bother to create an insert trigger that, by virtue of joining on the deleted table (which is NULL on insert), is incapapble doing anything?
Hi dqmq,
I always put the logic of INSERT, UPDATE and DELETE into one trigger to save having to worry about what order different triggers will be fired in and to save having the same code in multiple places if some code applies to more than one type of action. I cut and pasted this from an existing trigger which did more than just this - so that's why it is an INSERT and UPDATE trigger. For kevindockerty's requirements it can be reduced to just an UPDATE trigger, to save it even firing just for INSERT's.
<<With that solution you will need to enable nested triggers.>> - Why do you feel that is needed? We have Recursive Triggers turned off (and that is the best way to implement this solution, because of the risk of indefinite recursion) - in particular because we often update columns in the same table in a trigger and we definitely do not want the trigger to be fired again in those cases.
I always put the logic of INSERT, UPDATE and DELETE into one trigger to save having to worry about what order different triggers will be fired in and to save having the same code in multiple places if some code applies to more than one type of action. I cut and pasted this from an existing trigger which did more than just this - so that's why it is an INSERT and UPDATE trigger. For kevindockerty's requirements it can be reduced to just an UPDATE trigger, to save it even firing just for INSERT's.
<<With that solution you will need to enable nested triggers.>> - Why do you feel that is needed? We have Recursive Triggers turned off (and that is the best way to implement this solution, because of the risk of indefinite recursion) - in particular because we often update columns in the same table in a trigger and we definitely do not want the trigger to be fired again in those cases.
ASKER
thats cleared that one up for me - cheers lahousden - I had a pat on the back at work today thanks to you !
<<With that solution you will need to enable nested triggers.>> - Why do you feel that is needed?
I humbly withdraw that comment and feel totally stupid because I made the exact same mistake once before. My (incorrect) mindset was that recursive triggers do not fire unless that feature is turned on. Wrong. They do fire, but with recursion inhibited.
So, you are correct, recursive triggers do NOT need to be enabled, and most likely, should not be. And I congratulate you on a solution that works whether they are enabled or not.
I humbly withdraw that comment and feel totally stupid because I made the exact same mistake once before. My (incorrect) mindset was that recursive triggers do not fire unless that feature is turned on. Wrong. They do fire, but with recursion inhibited.
So, you are correct, recursive triggers do NOT need to be enabled, and most likely, should not be. And I congratulate you on a solution that works whether they are enabled or not.
Hi dqmq,
Thank you for your gracious remarks - raising the issue of the Recursive Triggers is instructive in this setting and was something I hadn't considered (since we have it turned off I never think about it), so is a valuable contribution to this discussion.
<<And I congratulate you on a solution that works whether they are enabled or not. >> - I was going to suggest that this was misplaced, since I was under the impression that if Recursive Triggers were turned on then the trigger would be in trouble. However, I have played this through on paper and I think we are also safe here, as you suggest.
This is how it goes; see if you agree with this:
1) A column other than the "modified" column is updated in a single row, causing the trigger to fire
2) The old and new values of the "modified" column are the same causing the trigger to update the "modified" column
3) Updating the "modified" column causes the trigger to fire again. However, this time the old and new values of the "modified" column are different, so the trigger won't update the row again.
4) We are done and OK.
So you are right - it should work whether Recursive Triggers are turned on or off (that is a relief!).
Thank you for your gracious remarks - raising the issue of the Recursive Triggers is instructive in this setting and was something I hadn't considered (since we have it turned off I never think about it), so is a valuable contribution to this discussion.
<<And I congratulate you on a solution that works whether they are enabled or not. >> - I was going to suggest that this was misplaced, since I was under the impression that if Recursive Triggers were turned on then the trigger would be in trouble. However, I have played this through on paper and I think we are also safe here, as you suggest.
This is how it goes; see if you agree with this:
1) A column other than the "modified" column is updated in a single row, causing the trigger to fire
2) The old and new values of the "modified" column are the same causing the trigger to update the "modified" column
3) Updating the "modified" column causes the trigger to fire again. However, this time the old and new values of the "modified" column are different, so the trigger won't update the row again.
4) We are done and OK.
So you are right - it should work whether Recursive Triggers are turned on or off (that is a relief!).
Yep, that's how I see it. The only clarification I would suggest is in point . The trigger fires once per update statement:
even when multiple rows are updated
even when "modified" is the only column updated
even when multiple rows are updated
even when "modified" is the only column updated
1. Stored Procedure layer -- require your updates to occur via a stored procedure that includes coding to automatically update the date. While, that may seem overkill, there are lots of other advantages to that approach; many practitioners consider it a best practice.
2. Update trigger. Technically, an "INSTEAD OF" trigger is proper because it avoids nested triggers. In the trigger you just issue an update statement for all rows that appear in the inserted table.