Trigger with insert statement
sql, sql-server, sql-server-2008
Solution
A trigger does not call itself recursively unless the `RECURSIVE_TRIGGERS` database option is set.
[This is done for obvious reasons!]
You can turn on/off at the database level with:
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
Ref: CREATE TRIGGER
Problem
I have employee table with columns (id, name, address, age). This trigger has another insert statement on same table. ``` create trigger emp_trigger on employee for insert as insert into employee (name, [address], age) values ('AutoCreated', 'inserted via trigger', 40) ``` Now when I insert something in employee table, trigger gets execute which inserts another entry in the table. However trigger does not gets execute further. I was expecting that it would create infinite loop, because the insert operation inside trigger would recursively call the trigger. I want to know why insert inside trigger does execute the trigger.