Microsoft® SQL Server® 2012 Bible

884

Part VII: Monitoring and Auditing

The following code creates a test INSTEAD OF trigger and then attempts to INSERT a row:

CREATE TRIGGER HumanResources.TriggerTwo ON

[HumanResources].[Department]

INSTEAD OF INSERT

AS

PRINT

'In the Instead of Trigger';

go

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

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

Result:

In the Instead of Trigger

(1 row(s) affected)

The result includes the INSTEAD OF trigger’s message and a report that one row was

affected. However, selecting Name = 'Datum Management'can prove that no rows were

inserted:

SELECT

[GroupName]

FROM

[HumanResources].[Department]

WHERE

[Name]

= 

'Datum Management'

;

Result:

GroupName

---------------

(0 row(s) affected)

The INSERT statement worked as if one row were affected; although the effect of the

INSERT statement was preempted by the INSTEAD OF trigger. The PRINT command was

executed instead of the rows being inserted. In addition, the AFTER trigger is still in

effect, but its PRINT message failed to print.

Trigger Limitations

Given their nature (code attached to tables), DML triggers have a few limitations. The fol-

lowing SQL commands are not permitted within a trigger:

c36.indd 884c36.indd 884 7/31/2012 10:03:07 AM7/31/2012 10:03:07 AM

http://www.it-ebooks.info