The Trouble with Triggers, Part I

hat is a trigger? How does it work? When should one use them, and how does one debug them? Last, what are the enhancements that were made in SQL Server version 7? Let’s explore some of these issues.

What Is a Trigger?
A trigger is a piece of code that SQL Server executes in response to a data modification statement; that is, an insert, update, or delete. The statement to create a trigger is:

  CREATE TRIGGER [owner.]trigger_nameON [owner.]table_nameFOR {INSERT, UPDATE, DELETE}[WITH ENCRYPTION]AS sql_statements

Unlike rules or defaults that can be attached to more than one column by using sp_bindrule or sp_bindefault, a trigger can only be attached to one table.

Each table can only have one trigger for each of the three possible events (insert, update, or delete); that is, you can’t have two triggers on a table responsible for handling a delete. A single trigger can handle from one to all of the events. In all, a table can have as few as zero triggers or as many as three.

When Is a Trigger Executed?
A trigger is implicitly part of the transaction that causes the data modification. However, SQL Server executes the trigger only after the table constraints have been checked.

Why Would I Want to Use One?
One of the most important uses for triggers used to be to enforce referential integrity. Correct me if I’m wrong, but SQL Server version 4.2 did not support referential integrity constraints such as foreign keys. Instead, you could create a trigger that, upon the insert of a record, looked for the corresponding value of the foreign key in the parent table. If the value wasn’t found, the transaction could be rolled back.

However, even though we now have support for foreign keys, sometimes triggers come in handy. For example, consider a system that uses an account number as the primary key in a table and contains another table that stores the transactions for each account such as debits and credits. Suppose that on occasion the account number must be changed. You can’t change the account number in the parent table (accounts) without first updating the value of the account number in the child table (transactions). A trigger is perfect for this sort of situation. (By the way, this situation is typically referred to as “cascading updates.”) In an update trigger on the account table, you place code that checks for the value that will change and updates all the corresponding records in the transaction table.

Similarly, a trigger is handy for keeping summary tables up to date. Continuing with our account table as an example, imagine a system that has many transactions coming in throughout the day for various accounts. It is important at any time for the users of this system to know what is the total value of the debits and credits transacted throughout the day. Depending on the number of transactions and the response time required, summarizing the information might take too long from the time when the user requests it. Instead, as each debit or credit is inserted into the transaction table, a trigger can be executed that contains code to update a summary table with the value of the transaction just entered.

Triggers are also useful for keeping audit logs of changes to sensitive data. The same trigger that updates the transaction table with the new value of the account can also insert a record into an audit table with the old account number, new account number, time changed, and the user name of the person that changed it.

In the future I’ll compare triggers to stored procedures and discuss how to code them. Look for examples for each of the situations we’ve discussed.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: