Intentionally recursive instead of insert trigger does not want to nest

Question

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?

Answer 1

An INSTEAD OF trigger will not directly call itself when performing operations.

Consider if you had wanted the trigger to insert the row only if it had an even value, it would look very close to the first statement and you wouldn't want it to recurse.

From the CREATE TRIGGER Page:

If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively. Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions.

That doesn't mean that INSTEAD OF can't be part of recursion. If the trigger makes changes to another table, it will call the triggers for those. The triggers could bounce back and forth and create recursion that way.

I believe that you can get the behavior you intend with a combination of INSTEAD OF to control the original insert and an AFTER trigger to add the additional row.