Table A triggers an insert onto Table B, Table B triggers an insert onto Table A, what happens?

Posted on

Question :

Assume I have two tables, table A and table B. Table A has an insert trigger that inserts a record into Table B. Table B has an insert trigger that inserts a record into Table A. What would happen in this case? Would the two triggers keep inserting records into the tables over and over until the database filled up? Is there some limit to how many times this will happen?

Answer :

Aside from the obvious “uh, don’t do that”… this took about two minutes to test.

USE tempdb;
GO

CREATE TABLE dbo.TableA(ID INT);

CREATE TABLE dbo.TableB(ID INT);

GO
CREATE TRIGGER dbo.TableAToB
ON dbo.TableA
FOR INSERT
AS
  INSERT dbo.TableB SELECT ID FROM inserted;
GO
CREATE TRIGGER dbo.TableBToA
ON dbo.TableB
FOR INSERT
AS
  INSERT dbo.TableA SELECT ID FROM inserted;
GO

INSERT dbo.TableA(ID) VALUES(1);

Error:

Msg 217, Level 16, State 1, Procedure TableBToA

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Also, no row is ever inserted into either table.

Leave a Reply

Your email address will not be published. Required fields are marked *