What does this operation mean please?
if @@trancount > 0 commit tran
and after that
SET IMPLICIT_TRANSACTIONS ON
SET IMPLICIT_TRANSACTIONS OFF
I do not understand the meaning of this sequence of commands even though I searched a lot online.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
I think there are two concepts that you need to understand:
IF @@trancount > 0 COMMIT TRANSACTION
This checks whether there are still any open transactions from earlier in the script, or that will be present if IMPLICIT_TRANSACTIONS are on. If you don't check for open transaction before doing a COMMIT, you will receive the following error:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
To simplify this, when IMPLICIT_TRANSACTIONS is ON, there is an invisible BEGIN TRANSACTION before specific statements (see list here) if there aren't any transactions open already. And when IMPLICIT_TRANSACTIONS is OFF, your statement is in autocommit mode
If you provide us with a code example, we might be able to answer your question more specifically. You can play with this code to see how these commands affect a statement:
/* SET IMPLICIT_TRANSACTIONS ON SET IMPLICIT_TRANSACTIONS OFF */ --BEGIN TRANSACTION --Uncomment if IMPLICIT_TRANSACTIONS is ON CREATE TABLE Test (col int) DROP TABLE Test SELECT @@TRANCOUNT BEGIN TRANSACTION SELECT @@TRANCOUNT COMMIT SELECT @@TRANCOUNT COMMIT SELECT @@TRANCOUNT --When IMPLICIT_TRANSACTIONS is ON, SQL will begin a new transaction count here, --You need to explicitly end the transaction at the end CREATE TABLE Test (col int) DROP TABLE Test SELECT @@TRANCOUNT IF @@TRANCOUNT > 0 COMMIT SELECT @@TRANCOUNT