Nested Triggers

Triggers can have as many as 16 nested levels. Nesting is set on at installation; you can turn trigger nesting on and off with the sp_configure system procedure. To disable nesting, use the following statement:

sp_configure 'nested triggers', 0

If nested triggers are enabled, a trigger that changes a table on which there is another trigger activates the second trigger, which can, in turn, activate a third trigger, and so on. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger terminates.

You can use nested triggers to perform useful housekeeping functions such as storing a backup copy of rows affected by a previous trigger. For example, you can create a trigger on titleauthor that saves a backup copy of the titleauthor rows that the delcascadetrig trigger deleted. With the delcascadetrig trigger in effect, deleting title_id PS2091 from titles deletes the corresponding row or rows from titleauthor. To save the data, you create a DELETE trigger on titleauthor that saves the deleted data into another separately created table, del_save.

CREATE TRIGGER savedel
ON titleauthor
FOR DELETE
AS
INSERT del_save
SELECT * FROM deleted

It is not a good idea to use nested triggers in an order-dependent sequence. Use separate triggers to cascade data modifications.

Note Because triggers are put into a transaction, a failure at any level of a set of nested triggers cancels the entire transaction, and all data modifications are rolled back. Include PRINT statements in your triggers so that you can determine where the failure occurred.

A trigger does not call itself recursively. In other words, a trigger does not call itself in response to a second update to the same table within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger activates only once rather than repeatedly.