SQL Server uses nested triggers

If a trigger triggers another trigger while executing the operation, the trigger then triggers the next trigger ...... These triggers are nested triggers. The trigger can be nested to 32 layers, and can control whether the trigger can be nested through the "nested trigger" server configuration option.

If a nested trigger is allowed and a trigger in the chain starts an infinite loop, the trigger exceeds the nesting level and ends.

You can use nested triggers to execute some useful daily work, such as saving a backup of the rows affected by the previous trigger. For example, you can create a trigger on titleauthor to save the backup of the titleauthor row deleted by the delcascadetrig trigger. When delcascadetrig is used, deleting title_id ps2091 from titles will delete the corresponding row or multiple lines in titleauthor. To save data, you can create a delete trigger on titleauthor to save the deleted data to another separately created table named del_save. 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. Individual triggers should be used to cascade data modifications.

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

Recursive trigger
Triggers are not manually called recursively, unless the recursive_triggers database option is set. There are two different recursive methods:

Direct Recursion
That is, the trigger is triggered and an operation is executed, and the operation triggers the same trigger again. For example, an applicationProgramThe trigger trig3 is triggered when table T3 is updated. Trig3 updates table T3 again, causing trigger trig3 to be triggered again.

Indirect Recursion
That is, a trigger is triggered and an operation is executed, which triggers a trigger in another table. The second trigger updates the original table and triggers the first trigger again. For example, an application updates table T1 and triggers trig1. Trig1 updates table T2 to trig2. Trig2 instead updates table T1 to trig1 again.

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

Example
A. Use recursive triggers to solve the self-reference relationship
A recursive trigger is used for a table with a self-reference relationship (also known as a transfer closure ). For example, the table emp_mgr defines:

An employee (EMP) of a company ).

The Manager (MGR) of each employee ).

Total number of employees (noofreports) reported to each manager in the organization tree ).
The recursive update trigger can keep the noofreports column up-to-date when a new employee record is inserted. The insert trigger updates the noofreports column of the Manager record, and this operation recursively updates the noofreports column of the management layer to other records.

Use pubs
Go
-- Turn recursive triggers on in the database.
Alter database pubs
Set recursive_triggers on
Go
Create Table emp_mgr (
EMP char (30) primary key,
Mgr char (30) 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 (30), @ M char (30)
Declare C1 cursor
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
-- this recursive update trigger works assuming:
-- 1. only Singleton updates on emp_mgr.
-- 2. no inserts in the middle of the 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 report.

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 report.
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 pre-update results:

EMP Mgr noofreports
----------------------------------------------------------------------
Alice Harry 2
Dave Joe 0
Harry null 1
Joe Alice 1
Paul Alice 0

The updated results are as follows:

EMP Mgr noofreports
----------------------------------------------------------------------
Alice Harry 2
Dave Harry 0
Harry null 2
Joe Alice 0
Paul Alice 0