Problem solve Get help with specific problems with your technologies, process and projects.

Troubleshooting triggers in SQL Server

A trigger is code that is executed when there is a specific data change in a table and is particularly important when you want to enforce your business logic.

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

Dig Deeper on Enterprise infrastructure management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.