what does "if @@trancount > 0 commit tran" mean?

Question

What does this operation mean please?

if @@trancount > 0 commit tran

and after that

SET IMPLICIT_TRANSACTIONS ON

or

SET IMPLICIT_TRANSACTIONS OFF

I do not understand the meaning of this sequence of commands even though I searched a lot online.

Answer 1

I think there are two concepts that you need to understand:

1. 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.

2. IMPLICIT_TRANSACTIONS

   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