CODEWITHSUNDEEP
sqltriggers
Brian Webster
Brian Webster

Reputation: 30865

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?

Upvotes: 0

Views: 1415

Answers (2)

Bill
Bill

Reputation: 1758

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.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40359

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)

Upvotes: 2

Related Questions