Sergiy (12/10/2007)
No bad experience.Except when trigger is badly written.
Like your one. 😉
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?