@@TRANCOUNT (Transact-SQL)

Returns the number of active transactions for the current connection.

Transact-SQL Syntax Conventions

Syntax

@@TRANCOUNT

Return Types

integer

Remarks

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

Examples

This example uses @@TRANCOUNT to test for open transactions that should be committed.

USE AdventureWorks;
GO
BEGIN TRANSACTION;
GO
UPDATE Person.Contact
    SET LastName = UPPER(LastName)
    WHERE LastName = 'Wood';
GO
IF @@TRANCOUNT > 0
BEGIN
    PRINT N'A transaction needs to be rolled back.';
    ROLLBACK TRANSACTION;
END

See Also

Reference

BEGIN TRANSACTION (Transact-SQL)
COMMIT TRANSACTION (Transact-SQL)
ROLLBACK TRANSACTION (Transact-SQL)
System Functions (Transact-SQL)

Other Resources

Nesting Transactions
Rollbacks and Commits in Stored Procedures and Triggers

Help and Information

Getting SQL Server 2005 Assistance