Microsoft® SQL Server® 2012 Bible

895

Chapter 36: Creating Triggers

36

Raiserror('Update failed.', 16, 1);

ROLLBACK;

Return;

End;

-- Update the Modified date

UPDATE Production.Product

SET ModifiedDate = CURRENT_TIMESTAMP

WHERE EXISTS

(SELECT *

FROM Inserted AS i

WHERE i.ProductID = Product.ProductID);

To test the trigger, the next INSERT command causes the trigger to print out a message

after a row has been inserted:

UPDATE Production.Product

SET [Name] = 'ModifiedDate Trigger'

WHERE ProductID = 999;

SELECT ProductID, ModifiedDate

FROM Production.Product

WHERE ProductID = 999;

Result:

ProductID ModifiedDate

999 2012-03-04 15:52:23.800

Instead Of and After Triggers Used Together

If a table has both an INSTEAD OF trigger and an AFTER trigger for the same event, then

the following sequence is possible:

1. The DML statement initiates a transaction.


2. The INSTEAD OF trigger fi res in place of the DML.


3. If the INSTEAD OF trigger executes DML against the same table event, then the
   process continues.


4. The AFTER trigger fi res.

Multiple After Triggers

If the same table event has multiple AFTER triggers, then they all execute. The order of the

triggers is less important than it may at fi rst seem.

c36.indd 895c36.indd 895 7/31/2012 10:03:09 AM7/31/2012 10:03:09 AM

http://www.it-ebooks.info