SQL Server 2008 R2 – Handling Recursive Instead of Insert Triggers

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?