VistaDB only supports a single open transaction (no nested transactions are supported). It is therefore very useful to be able to determine how many open transactions are present prior to creating one, or in an error handling loop.
Given a VistaDB Database like this( IVistaDBDatabase db ) you can query the NestedTransactionLevel property to determine how many transactions are open.
Open Transaction Count in DDA |
Copy Code
|
---|---|
if( db.NestedTransactionLevel > 0 )
{
db.RollbackTransaction();
}
|
In a SQL Proc you may want to check for transactions using the @@TRANCOUNT system variable.
The query will return 0 for none, or a positive number (only 1 is valid at this time). If no database is open or if the database is in an invalid state -1 will be returned to represent an error condition.
Open Transaction Count in SQL |
Copy Code
|
---|---|
SELECT @@TRANCOUNT; |
A stored procedure capable of handling an exception in a TSQL Proc that contains transactions should also check the TRANCOUNT and rollback if it is found to be greater than 0.
SQL Stored Procedure Example |
Copy Code
|
---|---|
BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH -- Do we have a transaction to rollback? IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END END CATCH |