honza.mf (10/1/2010)
Thank you Oleg, well done.It has only one little bug: You start new transaction allways. It is unsafe as you can nest several transactions.
I prefer in such a construct to test @@trancount = 1.
But what is more dangerous, you hide the rollback message, wich can be very important if you have something before the insert statement, just like you in your first example.
Thank you for pointing it out Honza. I just forgot to mention that by no means I advocate the snippet I wrote. I only did it to accentuate the difference between SQL Server 2000 and 2005 behaviour, which is in 2000 the error was not reported but in 2005 it is whenever the trancount on enter is not the same as trancount on exit. As far as execution is concerned, it is the same in both versions, that is the script after rollback tran inside of the trigger continues executing.
This was a very good question, as always.
Oleg