INF: @@TRANCOUNT Global Variable Detects Rollback
ID: Q68938
|
The information in this article applies to:
-
Microsoft SQL Server versions 4.2x, 6.0, 6.5, 7.0
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.
MORE INFORMATION
Please note that when writing a DB-Library (DB-Lib) program, @@TRANCOUNT is
global to one dbproc. If one DB-Library program opens two dbprocs to access
the same SQL Server, each dbproc will have its own local, independent
@@TRANCOUNT variable.
Additional query words:
Windows NT
Keywords : kbprg SSrvTrans SSrvServer
Version : winnt:4.2x,6.0,6.5,7.0
Platform : winnt
Issue type :