INF: @@TRANCOUNT Global Variable Detects Rollback (68938)

SUMMARY

@@TRANCOUNT is a global variable that contains the number of currently active transactions for the current user connection. This global variable can be used in the middle of a BEGIN TRANSACTION ... COMMIT TRANSACTION group to determine when and if that transaction has been rolled back.

The @@TRANCOUNT variable is incremented each time a new transaction is started with a BEGIN TRANSACTION command. It is decremented when a COMMIT TRANSACTION command is executed, or when the transaction has been rolled back. This global variable can be accessed by using the SELECT @@TRANCOUNT command.

When using only one transaction, @@TRANCOUNT will be 0 (zero) before the transaction. After the BEGIN TRANSACTION command is executed, @@TRANCOUNT will be 1 (one). If the transaction is completed successfully, @@TRANCOUNT will remain 1 until a COMMIT TRANSACTION command is performed. After this, @@TRANCOUNT will again be 0. However, if an error is encountered that causes the entire transaction to be rolled back, @@TRANCOUNT will be set to 0. This will occur before the COMMIT TRANSACTION command is executed.

This is especially useful when executing each statement of the transaction separately. Some errors will cause the transaction to be rolled back, while others will not. When you receive an error message, a quick check of @@TRANCOUNT will tell you whether your current transaction is still valid, or if it has been automatically rolled back.