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:

 

CREATETRIGGERTriggerCustomerUpdate

ONdbo.Customer

AFTERUPDATE

AS

BEGIN

UPDATE[dbo].Customer

SETModifiedDate=GETDATE()

WHERECustomer.CustomerId IN(SELECTDISTINCTInserted.CustomerIdFROMInserted)

END

Advertisements