Menu
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?
920
Triggers are said to be recursive/nested when a trigger on a table calls another trigger on same or different table and so on. The total numbers of recursive calls that can be made are limited to 32.
An INSTEAD OF trigger is a trigger that allows you to skip an INSERT , DELETE , or UPDATE statement to a table or a view and execute other statements defined in the trigger instead. The actual insert, delete, or update operation does not occur at all.
Direct recursion. This recursion occurs when a trigger fires and performs an action that causes the same trigger to fire again. For example, an application updates table T3; this causes trigger Trig3 to fire.
Triggers are the SQL codes that are automatically executed in response to certain events on a particular table. These are used to maintain the integrity of the data. A trigger in SQL works similar to a real-world trigger. For example, when the gun trigger is pulled a bullet is fired.
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.
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
