How to design and implement triggers

Your goal when dealing with triggers is the same as your goal with other database programs: Try to use as few resources as possible on the database side to achieve your objectives. Keep in mind that every SQL command is a resource consumer. Try to group commands as much as possible if doing so helps you boost performance.

1. Constraints, including check, referential integrity and cascading referential integrity, perform better than triggers. Cascading referential integrity constraints are automatic updates and deletes on dependant objects.

2. INSTEAD OF triggers are "executed after the inserted and deleted tables reflecting the changes to the base table are created, but before any other actions are taken. They are executed before any constraints," according to Books Online. This means that, from a performance viewpoint, the overhead of an INSTEAD OF trigger is higher than an AFTER trigger. Try to avoid INSTEAD OF triggers except in the following cases:

a. You need to make automatic updates to the same table after rows are inserted or after applying an update. Don't use conventional (AFTER) triggers that update the rows after you inserted and updated them. Use INSTEAD OF triggers that insert and update everything at once.
b. In SQL Server rollbacks are steep. If around 50% of the time the trigger would issue a rollback, use INSTEAD OF trigger. Its overhead is less than that of an AFTER trigger that rolls back.

3. You can create more than one trigger on a table (and on an action). You can also control which trigger should run first and which should run last. If you have more than two triggers on a table, you can't control the order in which the other ones fire. To optimize trigger performance, you should specify the trigger that is most likely to roll back (for whatever reason) as the first trigger to fire. This way, if the trigger does cause a rollback, less work is rolled back.

4. SQL Server Books Online states, "The use of cursors in triggers is not recommended because of the potentially negative impact on performance. Use rowset-based logic rather than cursors to design a trigger that affects multiple rows."

5. Avoid recursive triggers and nested triggers. The transaction length can greatly affect performance. Usually, a good application design leads you to stay away from needing those options.

6. Note that triggers execute (fire) once for each command and not for each affected row, whether only one row is involved or one million rows. This fact can help us deal with a number of performance issues.

How to optimize database triggers

 Home: Introduction
 Part 1: Why use triggers?
 Part 2: How to design and implement triggers
 Part 3: Example: INSTEAD OF triggers versus AFTER triggers
 Part 4: Example: One trigger versus two for the same logic
 Part 5: Example: Nested triggers
 Part 6: Example: Heavy insertion process from OLTP and triggers
 Part 7: Example: Cursors in triggers

ABOUT THE AUTHOR:

Michelle Gutzait Michelle Gutzait works as a senior databases consultant for ITERGY International Inc., an IT consulting firm specializing in the design, implementation, security and support of Microsoft products in the enterprise. Gutzait has been involved in IT for 20 years as a developer, business analyst and database consultant. For the past 10 years, she has worked exclusively with SQL Server. Her skills include database design, performance tuning, security, high availability, disaster recovery, very large databases, replication, T-SQL coding, DTS packages, and administrative and infrastructure tools development, reporting services and more. Copyright 2006 TechTarget

More from SearchSQLServer.com