Professional SQL Server™ 2005 Programming

13.4. Other Trigger Issues

You have most of it now, but if you're thinking you are finished with triggers, then think again. As I indicated early in the chapter, triggers create an awful lot to think about. The sections that follow attempt to point out some of the biggest issues you need to consider, plus provide some information on additional trigger features and possibilities.

13.4.1. Triggers Can Be Nested

A nested trigger is one that did not fire directly as a result of a statement that you issued but rather because of a statement that was issued by another trigger.

This can actually set off quite a chain of events — with one trigger causing another trigger to fire which, in turn, causes yet another trigger to fire, and so on. Just how deep the triggers can fire depends on:

• Whether nested triggers are turned on for your system (this is a system-wide, not database-level option; it is set using Management Studio or sp_configure, and defaults to on).

• Whether there is a limit of nesting to 32 levels deep.

• Whether a trigger has already been fired. A trigger can, by default, only be fired once per trigger transaction. Once fired, it will ignore any other calls as a result of activity that is part of the same trigger action. Once you move on to an entirely new statement (even within the same overall transaction), the process can start all over again.

In most circumstances, you actually want your triggers to nest (thus the default), but you need to think about what's going to happen ...