Writing Stored Procedures for Microsoft SQL Server

Problems with Triggers

The main two problems that you will encounter when using triggers are performance and concurrency issues. Chapter 16, "Considerations When Using Stored Procedures and Triggers," provides the steps you can take to alleviate these problems.

Although small, fast-executing triggers don't usually result in problems, large and complex triggers can result in performance problems. All triggers, nested or otherwise, execute serially. This action can result in overall transaction length issues that can cause problems in the client layer.

For example, I once worked on a system that had a trigger on the most active table in the database that, when printed, was 27 pages long. Not only that, but the trigger affected other tables in the ...