Posted By

jeffxl on 05/03/07


Tagged

date mssql last trigger modified created


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

zingo
huycaguy


Created Date & Last Modified Date Triggers


 / Published in: SQL
 

First, create two new fields in your table "Created", and "LastModified" as "datetime" fields, being sure to allow nulls. Then run this code as a query to create triggers that will update the appropriate field when a record is modified, or a record is inserted. This proves very useful when you're dealing with a huge database.

In the code below replace [TableName] with your actual table name, and replace [UniqueID] with a unique ID field name in your table.

  1. CREATE TRIGGER tr[TableName]CreateDate ON [TableName]
  2. FOR INSERT
  3. AS
  4. UPDATE [TableName] SET [TableName].Created=getdate()
  5. FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]
  6.  
  7. GO
  8.  
  9. CREATE TRIGGER tr[TableName]LastModifiedDate ON [TableName]
  10. FOR UPDATE
  11. AS
  12. UPDATE [TableName] SET [TableName].LastModified=getdate()
  13. FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]

Report this snippet  

Comments

RSS Icon Subscribe to comments
Posted By: sbarron1 on November 12, 2007

I can not get it to work. I copied the code and changed the table names and I keep getting this error.

Msg 8197, Level 16, State 4, Procedure trBrandCreateDate, Line 2 The object 'Brand' does not exist or is invalid for this operation.

Can you please help.

Posted By: huycaguy on December 10, 2007

Thanks. It works great!

Posted By: huycaguy on December 10, 2007

sbarron1: make sure you use created 2 fields in your table

Created -> datetime LastModified -> datetime

Replace [TableName] with your table name -> make sure correct table name

Posted By: peitor on September 2, 2010

I created a SQL script that generates these triggers for you

http://blog.gfader.com/2010/09/sql-server-generate-trigger-sql-script.html

You need to login to post a comment.