DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to create a timestamp trigger?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to create and test a timestamp trigger.

-- T-SQL update trigger - mssql timestamp trigger - using trigger to set timestamp
USE AdventureWorks;
 
/* Preparation
 sp_dboption 'AdventureWorks','recursive triggers', 'FALSE'
 ALTER DATABASE AdventureWorks SET RECURSIVE_TRIGGERS OFF
*/
-- SQL Server create trigger for UPDATE
CREATE
TRIGGER trgUpdateContact

ON Person.Contact

FOR UPDATE

AS

  BEGIN

    IF @@ROWCOUNT = 0   RETURN;

    IF TRIGGER_NESTLEVEL(object_ID('trgUpdateContact')) >RETURN;

    SET NOCOUNT  ON;

    

    UPDATE Person.Contact

    SET    Person.Contact.ModifiedDate = CURRENT_TIMESTAMP

    FROM   Person.Contact c

           INNER JOIN INSERTED i

             ON c.ContactID = i.ContactID

  END

GO


 
SELECT *
FROM   Person.Contact
WHERE  FirstName = 'Bailey'
       AND LastName = 'Collins'
GO
-- ModifiedDate: 2003-11-26 00:00:00.000
 
-- Updating a column will automatically update the ModifiedDate
UPDATE Person.Contact
SET    Phone = '212 484-3924'
WHERE  FirstName = 'Bailey'
       AND LastName = 'Collins'
GO
 
SELECT *
FROM   Person.Contact
WHERE  FirstName = 'Bailey'
       AND LastName = 'Collins'
GO
-- ModifiedDate: 2009-03-15 16:02:23.717
 
-- Cleanup
DROP TRIGGER Person.trgUpdateContact

------------
Related articles: Exploring SQL Server Triggers

Choice Between Stored Procedures, Functions, Views, Triggers, Inline SQL (updated)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE