Chapter 26. Creating DML Triggers
IN THIS CHAPTER
Using the transaction's data within the trigger
Integrating multiple triggers
deletetrigger 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.
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 ...