O'Reilly logo

Microsoft® SQL Server® 2008 Bible by Uttam Parui, Mike White, Paul Nielsen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required