First, everything is in some kind of “transaction”. When you do a “Begin Transaction”, you are stating that everything up to the next “Commit” (or Rollback) is to be considered as a single transaction. If you execute a SQL statement when there is no “Begin Transaction” active, that single statement is itself a transaction.
From the MSDN section on “Commits and Rollbacks in Stored Procedures and Triggers” (my emphasis added in places):
<b>A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.</b>
When a statement begins executing in autocommit mode, there is an implied BEGIN TRANSACTION to allow the recovery of all modifications generated by the statement if it encounters an error. This implied transaction has no effect on the other statements in the batch because it is either committed or rolled back when the statement completes. This implied transaction is still in effect, however, when a trigger is called.
When a trigger executes, an implicit transaction is started. If the trigger completes execution and @@TRANCOUNT = 0, error 3609 occurs and the batch is terminated. If a BEGIN TRANSACTION statement is issued in a trigger, it creates a nested transaction. In this situation, when a COMMIT TRANSACTION statement is executed, the statement will apply only to the nested transaction.
When using ROLLBACK TRANSACTION in a trigger, be aware of the following behavior:
All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.
The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back.
A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed. However, STATIC or INSENSITIVE cursors are left open if:
CURSOR_CLOSE_ON_COMMIT is set OFF.
The static cursor is either synchronous or a fully populated asynchronous cursor.
Instead of using ROLLBACK TRANSACTION, the SAVE TRANSACTION statement can be used to execute a partial rollback in a trigger.
Ok – so how do you prevent a rollback in your trigger from affecting the outer transaction/statement?
As the article says, use the “Save Transaction” statement, or just define your action as its own local transaction with “Begin Transaction” and then Commit or Rollback internally as appropriate, e.g.:
… trigger MyTrigger
declare @MyTransactionName varchar(32) = ‘My Local Tran’
Begin Transaction @MyTransactionName
… do something
… if error then
Rollback Transaction @MyTransactionName
… else if everything worked, then
Commit Transaction @MyTransactionName
Note that if you fail to properly close your transaction, an error will occur in the calling application when the SQL Server engine finishes the outer transaction and determines that the transaction counter isn’t 0.