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.
Best Answer
Your issue is related to the nested triggers notion in SQL Server. This concept is explained as like below in MSDN
Particularly your issue you can limit the
nested_triggers
config as 0 so that the triggers will be only performed one time.At first, we will set the
nested_triggers
value as0
. However, this option is on the server level to take account of this point.And then we will re-execute your query and the resultset of the a will be liked as below.