Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: enter image description here

Results I was expecting to see: enter image description here

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks for pointing me in the right direction. In the end I managed to accomplish the desired behavior with a combination of an after insert trigger, that does the update on the inserted record, and an after update trigger that inserts the new record, which will then in turn call the after insert trigger again and so on and so forth until my predefined nest level is reached. –  ilikebeets May 29 at 5:41
    
I also have to mention that I did it this way because of a restriction on the DB I was working on. The restriction was that I could not enable nested triggers for the DB since there is a bunch of other triggers that would start nesting if it was allowed on the DB. Thus my use of two After triggers (indirect recursion) as opposed to an Instead of and After trigger (direct recursion) which would then be stopped by the disabled nested triggers setting on the DB. –  ilikebeets May 29 at 5:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.