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?
Crazy situation.