SQL Server - transactions roll back on error

Question

We have client app that is running some SQL on a SQL Server 2005 such as the following   BEGIN TRAN  INSERT INTO myTable  myColumns      VALUES  myValues       INSERT INTO myTable  myColumns      VALUES  myValues       INSERT INTO myTable  myColumns      VALUES  myValues       COMMIT TRAN    It is sent by one long string command   If one of the inserts fail  or any part of the command fails  does SQL Server roll back the transaction   If it does not rollback  do I have to send a second command to roll it back   I can give specifics about the api and language I m using  but I would think SQL Server should respond the same for any language

User · Answer

You are correct in that the entire transaction will be rolled back. You should issue the command to roll it back.

You can wrap this in a TRY CATCH block as follows

BEGIN TRY
    BEGIN TRANSACTION

        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
        INSERT INTO myTable (myColumns ...) VALUES (myValues ...);

    COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    -- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
    RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH

User · Answer

From MDSN article  Controlling Transactions  Database Engine       If a run-time statement error  such as a constraint violation  occurs in a batch  the default behavior in the Database Engine is to roll back only the statement that generated the error  You can change this behavior using the SET XACT ABORT statement  After SET XACT ABORT ON is executed  any run-time statement error causes an automatic rollback of the current transaction  Compile errors  such as syntax errors  are not affected by SET XACT ABORT  For more information  see SET XACT ABORT  Transact-SQL     In your case it will rollback the complete transaction when any of inserts fail

User · Answer

You can put set xact abort on before your transaction to make sure sql rolls back automatically in case of error

User · Answer

Here the code with getting the error message working with MSSQL Server 2016   BEGIN TRY     BEGIN TRANSACTION          -- Do your stuff that might fail here     COMMIT END TRY BEGIN CATCH     IF   TRANCOUNT  gt  0         ROLLBACK TRAN          DECLARE  ErrorMessage NVARCHAR 4000    ERROR MESSAGE           DECLARE  ErrorSeverity INT   ERROR SEVERITY           DECLARE  ErrorState INT   ERROR STATE        -- Use RAISERROR inside the CATCH block to return error       -- information about the original error that caused       -- execution to jump to the CATCH block        RAISERROR   ErrorMessage   ErrorSeverity   ErrorState   END CATCH

User · Answer

If one of the inserts fail  or any part of the command fails  does SQL server roll back the transaction    No  it does not      If it does not rollback  do I have to send a second command to roll it back    Sure  you should issue ROLLBACK instead of COMMIT   If you want to decide whether to commit or rollback the transaction  you should remove the COMMIT sentence out of the statement  check the results of the inserts and then issue either COMMIT or ROLLBACK depending on the results of the check

[sql] SQL Server - transactions roll back on error?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to transactions