Transactions and Triggers

It is possible to work with transactions within a trigger issuing COMMIT TRANSACTION or ROLLBACK TRANSACTION statements. This may seem similar to having these statements within a T-SQL transaction, but it is not so with ROLLBACK.

Within a T-SQL batch, a ROLLBACK will undo any changes made from the start of the transaction, and processing will continue with the next statement after the ROLLBACK within the batch, or the next batch.

If we issue a ROLLBACK within a trigger, the changes are undone for the transaction including changes made in the trigger and the stored procedure at the BEGIN TRAN statement, but the batch will terminate and processing will move on to the next batch irrespective of any statements. The reason behind this can be seen in this section of dummy code:

     BEGIN TRANSACTION     UPDATE TableA     SET Column4 = Value1     WHERE column1 = Value2     INSERT INTO TableA (Column1,Column2,Column3,Column4)       VALUES (1,2,3,4)     COMMIT TRANSACTION     SELECT Column4 FROM TableA 

If we have a trigger on TableA and a problem arises within this batch, SQL Server will not assume that we have an UPDATE trigger, INSERT trigger, or both. The trigger with the ROLLBACK statement may be any of the ones set up for TableA, or on any combination of table updates. Therefore, the trigger itself may have been fired for the UPDATE or the INSERT. If a ROLLBACK is issued from the trigger, then SQL Server will not know whether it is allowed to run the INSERT or any further statements, so it takes the safe route and will not run the SELECT statement either (in the example).