Microsoft® SQL Server® 2012 Bible

889

Chapter 36: Creating Triggers

36

The scope of the inserted and deleted t a b l e s i s l i m i t e d t o t h e t r i g g e r. S t o r e d p r o c e d u r e s c a l l e d

by the trigger will not see these tables. The DML statement that fi red the trigger can out-

put the inserted and deleted tables using the OUTPUT clause.

For more details on the OUTPUT clause, refer to Chapter 12, “Modifying Data in SQL Server.”

The following example uses the inserted table to report the new values for the

GroupName column:

ALTER TRIGGER HumanResources.TriggerOne ON

HumanResources.Department

AFTER UPDATE

AS

SET NOCOUNT ON;

IF Update(GroupName)

SELECT 'You modified the GroupName column to '

 + Inserted.GroupName

FROM Inserted;

With TriggerOne implemented on the Person table, the following update modifi es a

GroupName value:

UPDATE

HumanResources.Department

SET GroupName = '

Analytics & Guessing

'

WHERE Name =

'Data Management';

Result:

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

You modified the GroupName column to

Analytics & Guessing

(1 row(s) affected)

Developing Multiple Row-Enabled Triggers

Many people do not write triggers to handle multiple-row INSERT, UPDATE, or DELETE

operations. They take a value from the inserted or deleted table and store it in a local

variable for processing. This technique checks only one of the rows affected by the DML

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

http://www.it-ebooks.info