Returns active transaction count for the current active connection. Every BEGIN TRAN increases count +1, and every COMMIT decreases by 1. ROLLBACK keyword resets all transactions to 0.
@@TRANCOUNT example:
USE AdventureWorks;
GO
//display transaction count///
SELECT @@TRANCOUNT AS 'Active Transaction Count';
//start 1th transaction///
BEGIN TRAN;
//start 2nd transaction///
BEGIN TRAN;
//display transaction count///
SELECT @@TRANCOUNT AS 'Active Transaction Count';
GO
//complete 1th transaction///
COMMIT;
SELECT @@TRANCOUNT AS 'Active Transaction Count';
ROLLBACK
Output:
......
Active Transaction Count
------------------------
0
(1 row(s) affected)
Active Transaction Count
------------------------
2
(1 row(s) affected)
Active Transaction Count
------------------------
1