I am working on a data warehouse project and would like to have a ModifiedDate added to my OLTP tables. I added the date field to the table design with a default set to GETDATE() so that when a row is inserted, the ModifiedDate would automatically be populated. However, I needed a way to handle when the record gets updated. The problem is, I don’t know when and in what cases those records are updated, whether by stored procedure or directly using entity framework, so I decided I needed a trigger. Turns out, triggers are pretty simple and powerful.

 

Here is an example of creating a trigger on my Customer table to manage the ModifiedDate field:

 

CREATE TRIGGER TriggerCustomerUpdate

ON dbo.Customer

AFTER UPDATE

AS

BEGIN

UPDATE [dbo].Customer

SET ModifiedDate = GETDATE()

WHERE Customer.CustomerId IN (SELECT DISTINCT Inserted.CustomerId FROM Inserted)

END