Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


The Trouble with Triggers, Part I

Part 1 of a three-part article on triggers in SQL Server. An overview of triggers in SQL Server: When to use them, how to debug them, and more.

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_name ON [owner.]table_name FOR {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.

Joe Lax has spent more than 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. He also is a MCSE and an MCT who recently has started to learn Oracle, which affords him no end of fun. He loves feedback and can be reached here.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date