sql - Update Trigger to populate a ModifyDate field

Question

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?

Thanks

Answer 1

UPDATE {tablename}
SET ModifyDate = GETDATE()
FROM inserted
WHERE {tablename}.{primarykey} = inserted.{primarykey}

Placed in a trigger tagged for INSERT and UPDATE actions will address your problem.

You could also do something similar for a CreateDate

UPDATE {tablename}
SET CreateDate = GETDATE()
FROM inserted
WHERE {tablename}.{primarykey} = inserted.{primarykey}

placed in a trigger tagged for INSERT action only.

Answer 2

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)