You do insert inside of INSTEAD OF INSERT.Besides it does not make much sense, if suddenly nested trigger are allowed - you are in trouble, man.

Not to stir up a hornet's nest - but I just checked that, and in 2005, that's actually not true Sergiy. Nested is on by default, and just for good measure, I also turned recursive triggers on... and it only inserts one copy of the data.

Maybe I didn't do this right, but David's code SHOULD work. And it's essentially right out of BOL on instead of triggers...

Granted though - in this case - the AFTER makes most sense (the INSTEAD would make more sense if you were filtering the INSERTS on the real table, and inserting everything into some external table for example....)All right - I'm now ready for the beating I am about to receive:):P

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

insert into real_table --will get posted to the current table regardless of type select col_list from inserted i

inside of INSTEAD OF INSERT trigger?

It will call the same trigger again and execute 1st insert from the trigger again.Then it it will come to insert into real_table and again - INSTEAD of inserting it will execute trigger code. And so on, 32 times.

Not sure if it will insert into real_table finally, but 1st insert will be executed 32 times.Before whole transaction will be rolled back because of the nesting error.

insert into real_table --will get posted to the current table regardless of type select col_list from inserted i

inside of INSTEAD OF INSERT trigger?

It will call the same trigger again and execute 1st insert from the trigger again.Then it it will come to insert into real_table and again - INSTEAD of inserting it will execute trigger code. And so on, 32 times.

Not sure if it will insert into real_table finally, but 1st insert will be executed 32 times.Before whole transaction will be rolled back because of the nesting error.

That's not the behavior that's happening here. With both nested triggers AND recursive triggers on (and restarting the service just to make sure) - it fires once and only once. Successfully.

No - really. I've never tried it before, so perhaps this is new (always did AFTER triggers and not INSTEAD OF), but the fact remains - it does work...

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (12/12/2007)That's not the behavior that's happening here. With both nested triggers AND recursive triggers on (and restarting the service just to make sure) - it fires once and only once. Successfully.

Well, it's not the behaviour I remembered when I wrote self-recurring trigger last time (2004?)It's either something is changed since then, or that was not INSTEAD OF trigger, or something else.Need to run some tests to understand what's the mechanics behind it.But probably a little bit later.

if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again. The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.

Appears I was wrong.Probably it was AFTER trigger which taught me never insert inserted recordset again.:)

Good thing you found that. I hadn't noticed that part, and was about to start a thread to figure that out. Like I said - I never used these guys (INSTEAD OF) before, and I was starting to wonder if I had broken recursion or something....;)

I was just tweaking it because I'd never managed to break SQL server in that particular way before....

Thanks for the reference.

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?