SQL Server-Table has many AFTER INSERT triggers that insert into the table itself

Question

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.

Answer 1

Your issue is related to the nested triggers notion in SQL Server. This concept is explained as like below in MSDN

Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels.

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 as 0. However, this option is on the server level to take account of this point.

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

And then we will re-execute your query and the resultset of the a will be liked as below.

+----+
| id |
+----+
|  0 |
|  2 |
|  1 |
+----+