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.'