Nesting Triggers

Triggers can nest to a depth of 16 levels. The current nesting level is stored in the @@nestlevel global variable.

Nesting is enabled at installation. A system administrator can use the allow nested triggers configuration parameter to turn trigger nesting on and off.

If nested triggers are enabled, a trigger that changes a table on which there is another trigger fires the second trigger, which can in turn fire a third trigger, and so forth. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger aborts. 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 titleauthor rows that was deleted by the delcascadetrig trigger. With the delcascadetrig trigger in effect, deleting the title_id “PS2091” from titles also deletes any corresponding rows from titleauthor. To save the data, you can create a delete trigger on titleauthor that saves the deleted data in another table, del_save:

create trigger savedel 
on titleauthor 
for delete 
as 
insert del_save 
select * from deleted

SAP suggests that you use nested triggers in an order-dependent sequence. Use separate triggers to cascade data modifications.

Note: When you put triggers into a transaction, a failure at any level of a set of nested triggers cancels the transaction and rolls back all data modifications. Use print or raiserror statements in your triggers to determine where failures occur.

A rollback transaction in a trigger at any nesting level rolls back the effects of each trigger and cancels the entire transaction. A rollback trigger affects only the nested triggers and the data modification statement that caused the initial trigger to fire.