I have been struggling to get an instead of trigger to recursively call itself.
The idea is that when a row gets inserted into my table the trigger is placed on, based upon certain conditions evaluated at that point, the row data should be modified and then inserted. Also when these conditions are satisfied a second row should be inserted into to the same table also causing the trigger to fire and evaluate the newly inserted values as before and modify the data and insert a second row as before and so on and so forth until the
TRIGGER_NESTLEVEL() reaches a specific value (always less than 3).
Try as I might, I cannot seem to get the trigger to execute itself again on the second insert.
Here is some sample code I wrote to attempt to illustrate my problem:
--delete Tester1 --go --drop table Tester1 alter database Playground set recursive_triggers on go create table Tester1(ID int identity, Val int, String varchar(255)) go Create trigger [tr_Tester1] on Tester1 instead of insert as Begin if(Select TRIGGER_NESTLEVEL()) >= 3 Return --Insert the initial row (modify text based on criterion) insert into Tester1 select inserted.Val, case when inserted.Val%2 = 0 then 'modified ' + inserted.String --Insert modified text else inserted.String --Insert the original text end from inserted --Insert a second row if the criterion was satisfied insert into Tester1 select inserted.Val * 2, --Insert a new value that should satisfy the condition 'some more text ' + CAST(TRIGGER_NESTLEVEL() as varchar(5)) FROM inserted where inserted.Val%2 = 0 End insert into Tester1(Val, String) values(2,'some text') select * from Tester1
Results I'm seeing:
Results I was expecting to see:
I have checked
SELECT DATABASEPROPERTYEX('Playground', 'IsRecursiveTriggersEnabled') and
select * from sys.configurations where name like 'nested triggers' and both are enabled even though according to MSDN:
"These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. INSTEAD OF triggers (only DML triggers can be INSTEAD OF triggers) can be nested regardless of this setting."
Also, I am aware that in my example my logic would always result in recursion (Well, theoretically at least). This is however only to illustrate the problem. In the actual trigger this is not necessarily the case.
Please can somebody point what I am missing or if I am misguided in my approach?