2

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.

  • 2
    have you really searched? I googled @@TRANCOUNT and IMPLICIT_TRANSACTIONS and the first item at the top of each list is the Microsoft online documentation. – DimUser Mar 5 '18 at 7:48
  • @DimUser yes, but I not understand the sequence of commands, what is the idea ? – srggd Mar 5 '18 at 7:49
3

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.