Microsoft® SQL Server® 2012 Bible

890

Part VII: Monitoring and Auditing

statement — a serious data integrity fl aw. They’ll even use cursors to step through each

affected row. This is the type of slow code that gives triggers a bad name.

Best Practice

The best way to deal with multiple rows is to work with the inserted and deleted tables via set-

oriented operations.

A join between the inserted table and the deleted or underlying table returns a com-

plete set of the rows affected by the DML statement. Table 36-3 lists the correct join combi-

nations for creating multirow-enabled triggers.

TABLE 36 -3 Multirow-Enabled FROM Clauses

DML Type FROM Clause

Insert FROM Inserted

Update FROM Inserted

INNER JOIN Deleted

ON Inserted.PK = Deleted.PK

Delete FROM Deleted

The following trigger sample alters TriggerOne to look at the inserted and deleted

tables:

ALTER TRIGGER HumanResources.TriggerOne ON

HumanResources.Department

AFTER UPDATE

AS

SELECT D.GroupName + ' changed to ' + I.GroupName

FROM Inserted AS I

INNER JOIN Deleted AS D

ON I.DepartmentID = D.DepartmentID

GO

UPDATE

HumanResources.Department

SET GroupName = '

Guessing & PowerPoints

'

WHERE GroupName = '

c36.indd 890c36.indd 890 7/31/2012 10:03:08 AM7/31/2012 10:03:08 AM

http://www.it-ebooks.info