Agility - CREATED DATE & LAST MODIFIED DATE TRIGGERS

pablop — Tue, 07 Oct 2008 12:41:00 GMT

It is common that for certain tables in our DBs we include the creation date and last modification date for every row.

This implies that we have to manually create a trigger for that.

I found the following script that saves our time and avoid to write it manually.

Simply:

- Create the columns in our table (here named ‘Created’ y ‘LastModified’) respectively.

- Find & replace ‘TableName’ and ‘UniqueID’.

That is. Simple, clear :).

The script:

CREATE TRIGGER tr[TableName]CreateDate ON [TableName]

FOR INSERT

UPDATE [TableName] SET [TableName].Created=getdate()

FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]

CREATE TRIGGER tr[TableName]LastModifiedDate ON [TableName]

FOR UPDATE

UPDATE [TableName] SET [TableName].LastModified=getdate()

FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]

Pablo Peralta's Blog : scritp