I purposely created this example to represent my problem.
I created table like this:
CREATE TABLE a ( id INT )
I then created 2 AFTER TRIGGER like this:
CREATE TRIGGER insert_a ON a AFTER INSERT AS BEGIN INSERT INTO a VALUES (1) END GO CREATE TRIGGER insert_a2 ON a AFTER INSERT AS BEGIN INSERT INTO a VALUES(2) END GO
After that I inserted into table:
INSERT INTO a VALUES (0)
I got the result :
Msg 217, Level 16, State 1, Procedure insert_a2, Line 5 [Batch Start Line 0].
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
I think there is cyclical situation here.The insert, inside the trigger, fires the trigger, and again and again. Will that happen?
I want to know what happens inside ?
Is there the way to solve this problem but still keep intact 2 AFTER INSERT triggers?