Wednesday, May 15, 2013

SQL Trigger: Updating a, "Modification_Date" with a Trigger

Never found a clear example online of simply updating a database's modification date with when a row is changed so I thought I would post one:

 =======================

 CREATE TRIGGER [dbo].[TRG_{TABLE-NAME}_Update_ModificationDate] ON [dbo].[{TABLE-NAME}]

AFTER UPDATE AS

 DECLARE @{UNIQUE-COLUMNNAME} [uniqueidentifier]

 SELECT @{UNIQUE-COLUMNNAME} = i.{UNIQUE-COLUMNNAME} FROM inserted i

UPDATE {TABLE-NAME} SET Modification_Date = GETDATE()
   WHERE {UNIQUE-COLUMNNAME} = @{UNIQUE-COLUMNNAME};

========================

Legend:
 {TABLE-NAME} = Name of Table to create the SQL Server Trigger on.
 {UNIQUE-COLUMNNAME} = Name of Column in table above that uniquely identifies a row.