By default, a trigger does not recursively call itself. 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.
