Microsoft® SQL Server® 2012 Bible

879

CHAPTER

36

Creating Triggers

IN THIS CHAPTER

Creating Instead of and After Triggers

Using the Transaction’s Data Within the Trigger

Integrating Multiple Triggers

Creating DDL Database Triggers

Preventing Server or Database Changes

Reading Event Data with XML

Understanding Security Triggers

S

QL Server triggers are special stored procedures attached to table events. They can’t be exe-

cuted directly, but fi re only in response to an INSERT, UPDATE, or DELETE event on a table.

Users can’t bypass a trigger; and unless the trigger sends a message to the client, the end

user is unaware of its actions.

Developing well-behaved triggers involves understanding transaction fl ow, locking, T-SQL, and

stored procedures. Triggers have a few unique elements that require careful planning, but they pro-

vide execution of complex business rules and data validation.

Trigger Basics

SQL Server triggers fi re once per data-modifi cation operation, not once per affected row. This may

seem to be a limitation, but developing set-based triggers actually helps ensure clean logic and fast

performance.

Triggers may be created for the three data-modifi cation commands: INSERT, UPDATE, and DELETE.

c36.indd 879c36.indd 879 7/31/2012 10:03:06 AM7/31/2012 10:03:06 AM

http://www.it-ebooks.info