Is INSERT initialization recursive or causes an infinite loop?

Question

I have a trigger INSERTon a table. When a row is inserted into the table, the trigger is triggered INSERT.

The trigger behavior is such that it inserts another row into the same table.

What would be the result of the operator INSERT?

Does this INSERTinfinite loop cause or only the expected 2 insertions?

Answer 1

This is a parameter in SQL - see the CREATE TRIGGER msdn page, in particular the section on recursive triggers. The setting you need to learn is RECURSIVE_TRIGGERS, if it is false, the trigger in table 1 will not trigger another insert into table1. If you enable recursive triggers, the limit is 32 levels.