Writing Stored Procedures for Microsoft SQL Server

Nested Triggers

Nested triggers aren't a special class of triggers, as the name might imply. Rather, nested triggers are fired in response to an action that's performed during another trigger. The second layer of triggers can then fire other triggers, and those can fire others, up until 32 levels of nesting. If a trigger chain exceeds 32 levels, SQL Server halts execution of the chain and returns an error. Trigger nesting can be turned off at the server level through the use of the nested triggers option, which is turned on by default.

There's another special class of nested triggers, known as recursive triggers. By default, when a trigger performs actions against the table on which it's defined, it won't fire off itself. For example, if you ...