CODEWITHSUNDEEP
sql-servert-sqltriggers
sumit
sumit

Reputation: 71

Is an INSERT trigger recursive, or cause an infinite loop?

I have an INSERT trigger on table. When a row is inserted in table, the INSERT trigger is fired.

The trigger's behaviour is such that it will insert another row into the same table.

What would be the result of the INSERT statement?

Does this INSERT result in an infinite loop, or just the expected 2 inserts?

Upvotes: 7

Views: 5321

Answers (1)

Mike M.
Mike M.

Reputation: 12521

This is a setting in SQL- see the CREATE TRIGGER msdn page, specifically the section on Recursive Triggers. The setting you need to look into is RECURSIVE_TRIGGERS, if this is false, a trigger on Table1 will not trigger another insert into Table1. If you do allow recursive triggers, the limit is 32 levels deep.

Upvotes: 8

Related Questions