Tuesday, August 6, 2013

Recursive Trigger Fire

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.