CODEWITHSUNDEEP
sql-server
HCL
HCL

Reputation: 36785

When is a nested trigger exeucted

When recursions on triggers is enabled in SQL Server and I update the same table within an after trigger, when is the nested (recursive) trigger executed:

Upvotes: 1

Views: 1339

Answers (1)

Martin Smith
Martin Smith

Reputation: 453608

CREATE TABLE T ( C INT  )

GO

CREATE TRIGGER TR_T
ON T 
AFTER UPDATE
AS
BEGIN
RAISERROR('TR - Entered @@NESTLEVEL=%d',0,1,@@NESTLEVEL)
IF @@NESTLEVEL < 5
     UPDATE T SET C=@@NESTLEVEL
RAISERROR('TR - Exited @@NESTLEVEL=%d',0,1,@@NESTLEVEL)
END


GO
SET NOCOUNT ON
UPDATE T SET C=12

Output

TR - Entered @@NESTLEVEL=1
TR - Entered @@NESTLEVEL=2
TR - Entered @@NESTLEVEL=3
TR - Entered @@NESTLEVEL=4
TR - Entered @@NESTLEVEL=5
TR - Exited @@NESTLEVEL=5
TR - Exited @@NESTLEVEL=4
TR - Exited @@NESTLEVEL=3
TR - Exited @@NESTLEVEL=2
TR - Exited @@NESTLEVEL=1

Conclusion: The child trigger fires when the DML statement in the parent trigger is executed then control is returned back to the parent trigger which can potentially execute additional code after the DML statement.

Upvotes: 1

Related Questions