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