;)
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
