Advanced Search

Return to Snippet

Revision: 2904
at May 3, 2007 10:52 by jeffxl
Initial Code 
CREATE TRIGGER tr[TableName]CreateDate ON [TableName]
FOR INSERT 
AS
UPDATE [TableName] SET [TableName].Created=getdate()
FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]

GO

CREATE TRIGGER tr[TableName]LastModifiedDate ON [TableName]
FOR UPDATE 
AS
UPDATE [TableName] SET [TableName].LastModified=getdate()
FROM [TableName] INNER JOIN Inserted ON [TableName].[UniqueID]= Inserted.[UniqueID]
Initial URL 

                                

                                                            

                                    

                                        

                                        Initial Description
                                        

                                    

                                    
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.

                                

                                                            

                                    

                                        

                                        Initial Title
                                        

                                    

                                    
Created Date & Last Modified Date Triggers

                                

                                                            

                                    

                                        

                                        Initial Tags
                                        

                                    

                                    
date

                                

                                                            

                                    

                                        

                                        Initial Language
                                        

                                    

                                    
SQL