|
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')) > 1 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)
|
