Brian Webster Brian Webster - 4 months ago 10
SQL Question

sql - Update Trigger to populate a ModifyDate field

I'm looking for an example of a good update trigger to update the ModifyDate field of a table.

It would be nice to handle the case where an update command updated more than one record in the table.

Is there a good template or tutorial for this?

Answer

Here's a cut and paste (and rename, to protect the innocent) of one I wrote quite some time ago (aka it works):

CREATE TRIGGER dbo.TR_iu_MyTable__LastUpdated
 on dbo.MyTable
 after insert, update

AS

    SET NOCOUNT on

    UPDATE dbo.MyTable
     set LastUpdated = getdate()
     where MyTableId in (select MyTableId from inserted)