Rollback in Stored Procedures and triggers

If the value of @ trancount is different from that of process execution when the stored procedure is completed, a 266 information error is generated. This error is not generated by the same condition in the trigger.

When a stored procedure is called, if @ trancount is 1 or larger and the process executes the rollback transaction or rollback work statement, error 266 is returned. This is because rollback rolls back all unfinished transactions and reduces @ trancount to 0, which is smaller than the call process.

If rollback transaction is triggered:

• All data modifications made to that point in the current transaction will be rolled back, including the changes made by the trigger.

• The trigger continues to execute all other statements after the rollback statement is executed. If any statement in these statements modifies data, these modifications are not rolled back. Executing other statements does not trigger nested triggers.
• In batch processing, all statements after the statement that triggers the trigger are not executed.
• The rollback in the trigger closes and releases all the declarations and opened cursors in the batch processing of statements containing the trigger. This includes the declared and opened cursor in the stored procedure called by the batch processing of the trigger. The cursor declared in the batch processing before initiating the trigger's batch processing will only be closed, but the static or insensitive cursor will not be closed under the following conditions:
  • Cursor_close_on_commit is set to off.

  • A static cursor is either a synchronous cursor or a fully filled asynchronous cursor.

When a trigger is executed, the trigger operation always seems to have an unfinished transaction working. If the statement that triggers the trigger is in a implicit or explicit transaction, this will certainly happen. This is also true in automatic submission mode. When a statement starts to run in automatic submission mode, if an error occurs, an implicit begin transaction statement will allow you to resume all modifications generated by this statement. This implicit transaction has no impact on other statements in the batch processing, because when the statement is completed, the transaction is either committed or rolled back. However, when the trigger is called, the implied transaction will still be valid.

This means that as long as the begin transaction statement is issued in the trigger, a nested transaction is actually started. When a nested transaction is rolled back, the nested begin transaction statement is ignored. The rollback transaction in the trigger always rolls back all the begin transaction statements issued by the trigger. Rollback rolls back to the external begin transaction.

To perform partial rollback in a trigger, you must use the save transaction statement even if the call is always in the automatic submission mode. The following triggers clarify this:

 
Create trigger testtrig on testtab for update assave transaction mynameinsert into testauditselect * From insertedif (@ error <> 0) beginrollback transaction mynameend
 

This also affects the commit transaction statement after the begin transaction statement in the trigger. Because begin transaction starts a nested transaction, the subsequent commit statement only applies to this nested transaction. If you execute the rollback transaction statement after the commit statement, the rollback will always roll back to the most external begin transaction. The following triggers clarify this:

Create trigger testtrig on testtab for update asbegin transactioninsert into trigtargetselect * From insertedcommit transactionrollback transaction
 

This trigger will neverTrigtargetInsert a table. Begin transaction always starts a nested transaction. Commit transaction only commits nested transactions, while the following rollback transaction rolls back to the most external begin transaction.