Transact-SQL User's Guide-Nesting triggers

Transact-SQL User's Guide

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 turn trigger nesting on and off with the allow nested triggers configuration parameter.

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 the corresponding row(s) 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

It is not a good idea to use nested triggers in an order-dependent sequence. Use separate triggers to cascade data modifications, as in the earlier example of delcascadetrig, described under "Cascading Delete Example".

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.

Trigger Self-Recursion

By default, a trigger does not call itself recursively. That is, an update trigger does not call itself in response to a second update to the same table within the trigger. If an update trigger on one column of a table results in an update to another column, the update trigger fires only once. However, you can turn on the self_recursion option of the set command to allow triggers to call themselves recursively. The allow nested triggers configuration variable must also be enabled for self-recursion to occur.

The self_recursion setting remains in effect only for the duration of a current client session. If the option is set as part of a trigger, its effect is limited by the scope of the trigger that sets it. If the trigger that sets self_recursion on returns or causes another trigger to fire, this option reverts to off. Once a trigger turns on the self_recursion option, it can repeatedly loop, if its own actions cause it to fire again, but it cannot exceed the limit of 16 nesting levels.

For example, assume that the following new_budget table exists in pubs2:

select * from
new_budget

unit            parent_unit
budget
--------------- --------------- -------
one_department
one_division    10
one_division    company_wide
100
company_wide    NULL            1000
 
(3 rows
affected)

You can create a trigger that recursively updates new_budget whenever its budget column is changed, as follows:

create trigger budget_change
on new_budget
for
update as
if exists (select * from inserted
            where
parent_unit is not null)
begin
    set self_recursion on

update new_budget
    set new_budget.budget = new_budget.budget +

      inserted.budget - deleted.budget
    from inserted, deleted,
new_budget
    where new_budget.unit = inserted.parent_unit

and new_budget.unit = deleted.parent_unit
end

If you update new_budget.budget by increasing the budget of unit one_department by 3, Adaptive Server behaves as follows (assuming that nested triggers are enabled):

Increasing one_department from 10 to 13 fires the budget_change trigger.
The trigger updates the budget of the parent of one_department (in this case one_division) from 100 to 103, which fires the trigger again.
The trigger updates the parent of one_division (in this case company_wide) from 1000 to 1003, which causes the trigger to fire for the third time.
The trigger attempts to update the parent of company_wide, but since none exists (the value is "NULL"), the last update never occurs and the trigger is not fired, ending the self-recursion. You can query new_budget to see the final results, as follows:
```
select * from new_budget
```
```
unit
     parent_unit     budget
--------------- ---------------
-------
one_department  one_division    13
one_division
company_wide    103
company_wide    NULL            1003
 
(3
rows affected)
```

A trigger can also be recursively executed in other ways. A trigger calls a stored procedure that performs actions that cause the trigger to fire again (it is reactivated only if nested triggers are enabled). Unless conditions within the trigger limit the number of recursions, the nesting level can overflow.

For example, if an update trigger calls a stored procedure that performs an update, the trigger and stored procedure execute only once if nested triggers is set to off. If nested triggers is set to on, and the number of updates exceeds 16 by some condition in the trigger or procedure, the loop continues until the trigger or procedure exceeds the 16-level maximum nesting value.