Microsoft® SQL Server® 2012 Bible

883

Chapter 36: Creating Triggers

36

The following AFTER trigger simply prints 'In the After Trigger' when the trigger

executes:

USE AdventureWorks2012;

CREATE TRIGGER HumanResources.TriggerOne ON

[HumanResources].[Department]

AFTER INSERT

AS

PRINT 'In the After Trigger';

With the AFTER trigger enforced, the following code inserts a sample row:

INSERT [HumanResources].[Department](Name, GroupName)

VALUES ('Data Management', 'Hording & Forecasting');

Result:

In the After Trigger

(1 row(s) affected)

The INSERT worked and the trigger printed the message.

Instead of Triggers

INSTEAD OF triggers execute “instead of” (as a substitute for) the submitted DML state-

ment. This is why such triggers are not classifi ed as BEFORE triggers because they preempt

the statement’s normal execution.

As a substitution procedure, each table is limited to only one INSTEAD OF trigger per table

event. In addition, INSTEAD OF triggers may be applied to views as well as tables.

INSTEAD OF triggers are useful when the DML statement fi ring the trigger will always

be rolled back and some other logic will be executed instead of the DML statement, for

example:

■ (^) When the DML statement attempts to update a nonupdatable view, the
INSTEAD OF trigger updates the underlying tables instead.
■ (^) When the DML statement attempts to directly update an inventory table, an
INSTEAD OF trigger updates the inventory transaction table instead.
■ (^) When deleting rows, an INSTEAD OF trigger moves them to an archive table
instead.
c36.indd 883c36.indd 883 7/31/2012 10:03:07 AM7/31/2012 10:03:07 AM
http://www.it-ebooks.info