Advanced Search

Created Date & Last Modified Date Triggers

/ Published in: SQL
Save to your folder(s)
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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

You need to login to post a comment.