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?
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);
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.