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.
Posted
by: MR. JOYDEEP DAS
No comments:
Post a Comment