Tip

Troubleshooting triggers in SQL Server

A trigger is code that is executed when there is a specific data change in a table. Triggers are one way of controlling how your database responds to INSERTs, DELETEs, and UPDATEs and can be used to maintain data integrity. Triggers are used whenever a constraint placed on data modification is not robust enough to serve your purpose. A trigger is similar to a stored procedure, but is automatically executed when one of those three statements are invoked. Triggers are particularly important when you want to enforce your business logic.

There are several reasons that a trigger may fail to run. Any action that INSERTs into a table non-logged may cause a trigger to fail to run if the database is using either a simple or bulk logged recovery method. If you use the BCP command to copy data between SQL Server and a data file using a format that the user specifies, you'll find that doing so will also cause triggers to fail. BCP bypasses triggers, as well as rules or CHECK constraints that you might have in place.

You also can run into trouble when you set nested triggers to "0" (a server option). In that state, your system will not be able to perform indirect recursion, and thus more than one trigger can't fire for a single event. If you use nested triggers then you need to avoid this setting.

Another problem occurs when you use the TRUNCATE TABLE command. This command also will cause any DELETE triggers you have in place to fail to fire.

There are several

    Requires Free Membership to View

reasons why a trigger may take a long time to execute. The thing to check is the number of referenced tables and rows being used by the trigger code. Triggers work best when they are small code operating on small amounts of data. If your trigger uses a SELECT operation, then make sure that there is an index available to support that operation. SELECTs without an index can be slow. Consider checking your trigger with the query optimizer to see if you can improve its performance.

Triggers are meant to enforce business logic and aren't as fast as some of the built-in SQL Server tools. Triggers shouldn't replace SQL Server's referential integrity tools. In most cases, when you can use a CHECK constraint you will want to opt for that as it faster than a trigger. Still triggers are particularly valuable and can help you maintain data integrity.


Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield, Mass.). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.