Final Words on Developing Triggers

The following is a short list of recommendations when using triggers:

1. Use triggers when necessary to enforce business rules and integrity not adequately handled by the built-in constraints.

2. Keep trigger code simple. If there is a lot you need to accomplish in a trigger, then consider breaking your code into more than one trigger, which is akin to how you write code in traditional programming environments.

3. Be sure to check for excessive recursion.

4. Use NOCOUNT to suppress the “n rows affected” message returned to the connection. And don’t leave result sets open or unassigned. Use them only for the benefit of the trigger, such as by using SELECT to find values or to compare values in multiple tables.

5. Minimize the use of ROLLBACK TRANSACTION.

6. If your triggers begin to look like general procedural code, requiring the return of result sets to clients, and functionality beyond integrity and enforcement of business rules, then you need to switch to a stored procedure, a function, or managed code.

Now, let’s look at two more complicated triggers.