rtelgenhoff (10/1/2010)
It's interesting that even though the first INSERT statement is not part of an explicit transaction, the ROLLBACK inside the trigger negates the INSERT. If there was no trigger defined, an INSERT without a preceding BEGIN TRAN, followed by a ROLLBACK produces the error 'Msg 3903, Level 16, State 1, Line 2 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.'
That behavior is governed by SQL Server's default behavior of Autocommit transactions. Because the trigger executes within the context of the original transaction, as long as BOTH the trigger and the statement causing trigger execution are successful, then the original transaction is committed automatically.