Microsoft® SQL Server® 2008 Bible

Chapter 26. Creating DML Triggers

IN THIS CHAPTER

• Creating instead of and after triggers

• Using the transaction's data within the trigger

• Integrating multiple triggers

• Building a delete trigger to enforce a single-row delete

Triggers are special stored procedures attached to table events. They can't be directly executed; they fire only in response to an INSERT, UPDATE, or DELETE event on a table. In the same way that attaching code to a form or control event in Visual Basic or Access causes that code to execute on the form or control event, triggers fire on table events. Users can't bypass a trigger; and unless the trigger sends a message to the client, the end-user is unaware of the trigger.

Developing triggers involves several SQL Server topics. Understanding transaction flow and locking, T-SQL, and stored procedures is a prerequisite for developing smooth triggers. Triggers contain a few unique elements and require careful planning, but they provide rock-solid execution of complex business rules and data validation.

Trigger Basics

SQL Server triggers fire once per data-modification operation, not once per affected row. This is different from Oracle, which can fire a trigger once per operation, or once per row. While this may seem at first glance to be a limitation, being forced to develop set-based triggers actually helps ensure clean logic and fast performance.

Triggers may be created for the three table events that correspond to the three data-modification commands: INSERT, UPDATE, and ...