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.

Answer 1

You can try wrapping this in a TRY CATCH block like this:

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

You are right, in the query that you have mentioned in question the entire transaction will be rolled back. You have to issue the command to roll it back.