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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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