3

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?

5

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.

  • Thanks! I knew it was a bad idea I was curious as to just how bad. I didn't have a server I was willing to take down to test it. – Chani_Of_Dune Apr 25 '14 at 20:49
  • 2
    @chani the most you'd need to do is download SQL Server Express with Management Studio from Microsoft's download site, install it, and try it out. You should always have a development machine for testing purposes. – Max Vernon Apr 26 '14 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.