Recursive Trigger Fire
Introduction
We never design database like that the recursive trigger fire. But what happens if recursive trigger happens? We have to understand it clearly and solve such kind of problem. This article is related to it. Hope it will be informative.
First We understand it?
We have two tables named Test_1 and Test_2. The Test_1 have trigger named trg_Test_1 which insert data to table Test_2 and Test_2 table have trigger named trg_Test_2 which Insert data to Test_1 Table.
Example of Recursive Trigger
Step-1 [ Creating Base Table ]
CREATE TABLE test_1
(
ID INT,
MARKS INT
)
GO
CREATE TABLE test_2
(
ID INT,
MARKS INT
)
GO
Step-2 [ Creating Trigger ]
CREATE TRIGGER trg_test_1 ON test_1
AFTER INSERT
AS
BEGIN
INSERT INTO test_2
SELECT * FROM Inserted;
END
GO
CREATE TRIGGER trg_test_2 ON test_2
AFTER INSERT
AS
BEGIN
INSERT INTO test_1
SELECT * FROM Inserted;
END
Step-3 [ Insert Value in First Table so that Trigger can Fire ]
INSERT INTO test_1
VALUES(1, 20);
Msg 217, Level 16, State 1, Procedure trg_test_2, Line 5
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
If we look at the table we find no records is affected
SELECT * FROM test_1
SELECT * FROM test_2
Step-4 [ Now we Rectified the Trigger ]
DROP TRIGGER trg_test_1
GO
CREATE TRIGGER trg_test_1 ON test_1
AFTER INSERT
AS
BEGIN
IF trigger_nestlevel() < 2
BEGIN
INSERT INTO test_2
SELECT * FROM Inserted;
END
END
GO
DROP TRIGGER trg_test_2
GO
CREATE TRIGGER trg_test_2 ON test_2
AFTER INSERT
AS
BEGIN
IF trigger_nestlevel() < 2
BEGIN
INSERT INTO test_1
SELECT * FROM Inserted;
END
END
Step-5 [Observation ]
INSERT INTO test_1
VALUES(1, 20);
SELECT * FROM test_1
SELECT * FROM test_2
ID MARKS
----------- -----------
1 20
(1 row(s) affected)
ID MARKS
----------- -----------
1 20
(1 row(s) affected)
Hope you like it.
