Why use triggers?

SQL Server Books Online states that "triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT or DELETE statement is issued against a table or view."

The problem with triggers is that they cannot be "seen." It is easy to view table relationships, constraints and indexes in the database. On the client-application side, it is also simple to analyze the code. Triggers, on the other hand, are automatic programs defined in the database that execute behind the scenes as part of the command that fired them. It is hard to follow their logic and, in time, it is easy to forget about triggers, especially if they are not well-documented.

Here are some trigger design tips to keep in mind:

1. Avoid using nested triggers
By default, if a trigger is changing other tables, the triggers declared for these tables are not fired. The "allow nested triggers" server option sets databases to have the opposite behavior. Triggers are nested when a trigger performs an action that initiates another trigger, which can initiate another trigger and so on. Triggers can be nested up to 32 levels. It is very difficult to follow the logic of nested triggers and they can affect performance.

2. Avoid using recursive triggers
There are two types of recursion:

• Direct recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. You can prevent that from happening by setting the "recursive trigger" database option to OFF.
• Indirect recursion occurs when a trigger fires and performs an action that causes a trigger on another table to fire. This second trigger causes an update to occur on the original table, which causes the original trigger to fire again. This can be prevented with the "nested triggers" server option.

Note that INSTEAD OF triggers commonly have to perform at least one SQL command (UPDATE/INSERT/DELETE) as they are executed, instead of the command that fired them. You have to design them carefully keeping in mind upcoming schema changes. Sometimes you simply have to hard code the field list and remember to change it in the future as needed. As an example, if you have to insert a row into a table with an identity column, you can't just insert into <table> select * from <another table>.

In this case, you have to state all the required fields, except for the one that holds the identity values.

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