SQL Server uses nested triggers

server| triggers if a trigger throws another trigger while the action is executing, and the trigger then throws the next trigger ... These triggers are nested triggers. Triggers can be nested to layer 32 and can control whether triggers can be nested through the nested triggers server configuration option.

If nested triggers are allowed, and a trigger in the chain begins an infinite loop, the nesting level is exceeded and the trigger terminates.

You can use nested triggers to perform some useful daily tasks, such as saving a backup of the rows affected by the previous trigger. For example, you can create a trigger on the titleauthor to hold a backup of the titleauthor row that was deleted by the Delcascadetrig trigger. When you use Delcascadetrig, removing title_id PS2091 from titles deletes the corresponding row or rows in the titleauthor. To save data, you create a delete trigger on the titleauthor that saves the deleted data to another individually created Del_save table. For example:

CREATE TRIGGER Savedel
On titleauthor
For DELETE
As
INSERT Del_save
SELECT * from deleted

Nested triggers are not recommended for sequence-dependent sequences. You should cascade data modifications using a separate trigger.


Note Because a trigger executes in a transaction, if an error occurs in any layer of a series of nested triggers, the entire transaction is canceled and all data modifications are rolled back. Include the PRINT statement in the trigger to determine where the error occurred.


recursive triggers
Triggers are not invoked recursively, unless the Recursive_triggers database option is set. There are two different ways of recursion:

Direct recursion
The trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates the table T3, which triggers the trigger Trig3. TRIG3 updates the table T3 again, causing the trigger Trig3 to be raised again.

Indirect recursion
The trigger fires and performs an action that causes a trigger in another table to fire. The second trigger causes the original table to be updated so that the first trigger is raised again. For example, an application updates the table T1 and throws a trigger Trig1. TRIG1 updates the table T2 so that the trigger Trig2 is raised. Trig2 instead updates the table T1 so that TRIG1 is raised again.

When the recursive_triggers database option is set to OFF, only direct recursion is prevented. To also disable indirect recursion, set the nested triggers server option to 0.

Sample
A. Using recursive triggers to resolve A self-referencing relationship
One use of recursive triggers is for tables with a self referencing relationship (also known as transitive closures). For example, the table Emp_mgr defines:

An employee of a company (EMP).


Manager of each employee (MGR).


Total number of employees reported to each manager in the organization Tree (NoOfReports).
The recursive UPDATE trigger keeps the NoOfReports column up to date when a new employee record is inserted. The INSERT trigger updates the NoOfReports column of the manager record, which recursively updates the NoOfReports column of other records on the management level.

Use pubs
Go
--Turn recursive triggers on the database.
ALTER DATABASE Pubs
SET Recursive_triggers on
Go
CREATE TABLE Emp_mgr (
EMP char (PRIMARY KEY),
Mgr Char () NULL FOREIGN KEY REFERENCES emp_mgr (EMP),
NoOfReports int DEFAULT 0
)
Go
CREATE TRIGGER emp_mgrins on Emp_mgr
For INSERT
As
DECLARE @e char (m), @m char (30)
DECLARE C1 CURSOR for
SELECT emp_mgr.emp
From Emp_mgr, inserted
WHERE emp_mgr.emp = inserted.mgr

OPEN C1
FETCH NEXT from C1 into @e
While @ @fetch_status = 0
BEGIN
UPDATE Emp_mgr
SET Emp_mgr. NoOfReports = Emp_mgr. NoOfReports + 1--ADD 1 for newly
WHERE emp_mgr.emp = @e-Added employee.

FETCH NEXT from C1 into @e
End
Close C1
DEALLOCATE C1
Go
--Recursive UPDATE trigger works assuming:
--1. Only singleton updates on emp_mgr.
--2. No inserts in the middle's org tree.
CREATE TRIGGER emp_mgrupd on emp_mgr for UPDATE
As
IF UPDATE (MGR)
BEGIN
UPDATE Emp_mgr
SET Emp_mgr. NoOfReports = Emp_mgr. NoOfReports + 1--Increment Mgr ' s
From inserted--(No. of reports) by
WHERE emp_mgr.emp = inserted.mgr-1 for the new.

UPDATE Emp_mgr
SET Emp_mgr. NoOfReports = Emp_mgr. NoOfReports-1--Decrement Mgr ' s
From deleted--(No. of reports) by 1
WHERE emp_mgr.emp = deleted.mgr-for the new.
End
Go
--Insert some test data rows.
INSERT emp_mgr (EMP, MGR) VALUES (' Harry ', NULL)
INSERT emp_mgr (EMP, MGR) VALUES (' Alice ', ' Harry ')
INSERT emp_mgr (EMP, MGR) VALUES (' Paul ', ' Alice ')
INSERT emp_mgr (EMP, MGR) VALUES (' Joe ', ' Alice ')
INSERT emp_mgr (EMP, MGR) VALUES (' Dave ', ' Joe ')
Go
SELECT * from Emp_mgr
Go
--Change Dave's manager from Joe to Harry
UPDATE emp_mgr SET mgr = ' Harry '
WHERE emp = ' Dave '
Go
SELECT * from Emp_mgr
Go

The following are the results before the update:

EMP Mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Joe 0
Harry NULL 1
Joe Alice 1
Paul Alice 0

The following are the updated results:

EMP Mgr NoOfReports
------------------------------ ----------------------------- -----------
Alice Harry 2
Dave Harry 0.
Harry NULL 2
Joe Alice 0
Paul Alice 0