Chapter 26. Creating DML Triggers


  • 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 ...

Get Microsoft® SQL Server® 2008 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.