Need an update trigger to log last update to table

kevindockerty
kevindockerty used Ask the Experts™
on
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




Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
This is SQL Server right?  There is no way to do it declaratively.  Basically, there are two approaches:
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.
Yep - we do this for many tables.  You will need a datetime column (called, for example, "modified").

CREATE TRIGGER MyTable_iut ON dbo.[MyTable]
FOR INSERT, UPDATE
AS
 begin
  update m
  set modified = GetDate()
  from inserted  as i
  join deleted   as d on d.PK = i.PK
  join MyTable as m on m.PK = i.PK
  where (i.modified = d.modified)
  or    (i.modified is null and d.modified is null)
 end

This allows you to set "modified" explicitly to something else if you need to (e.g. usign Query Analyzer or Enterprise Manager) - otherwise, whenever you update other columns in the table "modified" will be set to the time of the update.

Author

Commented:
thanks
just out of interest - why include 'deleted' in this solution ?
would the pseudo row 'deleted' even exist in an insert,update trigger ?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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.

 

Commented:
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?

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.

Author

Commented:
thats cleared that one up for me - cheers lahousden - I had a pat on the back at work today thanks to you !

Commented:
<<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.


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!).

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial