SQL Server™ 2005 Bible

Chapter 23. Implementing 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 provide rock-solid execution of complex business rules and data validation.

Some DBAs oppose the use of triggers because they are proprietary in nature. If the database is ported to another platform, most trigger code will have to be rewritten. Triggers are also accused of hindering performance. In defense of triggers, if a rule is too complex for a constraint, then a trigger is the only other acceptable location for it. A business rule implemented outside the server is not a rule; it's a suggestion. If a trigger is poorly written, it will have a significant ...