advanced code snippet search

Posted By

jeffxl on 05/03/07


Tagged

date mssql last trigger modified created


Versions (?)


Advertising

Website Promotion DIRECTORY is a crucial factor for all websites that need to gain better organic search engine rankings and increase website traffic.
Submitting your website as part of your Web Promotion strategy to our SEO friendly and high traffic Business Directory for review is an excellent way to gain a valuable backlink and increase your websites visibility online.

Submit Site


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.

Expand | Embed | Plain Text 
  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

You need to login to post a comment.

Download royalty free graphics