Any ideas for ROLLBACK TRANSACTION on this SQL statement

Question

Can someone help me with this SQL statement. I run it on the SQL Server engine.

I have the following statement that removes all entries in the table and replaces them with new ones:

SET XACT_ABORT ON;
BEGIN TRANSACTION;
DELETE FROM [t1] WHERE [id]>10;
INSERT INTO [t1] ([id], [v2], [v3]) SELECT COALESCE(MAX([id]), 0)+1, 'a1', 'b1' FROM [t1];
INSERT INTO [t1] ([id], [v2], [v3]) SELECT COALESCE(MAX([id]), 0)+1, 'a2', 'b2' FROM [t1];
--and so on, I may have up to 100 of these inserts
INSERT INTO [t1] ([id], [v2], [v3]) SELECT COALESCE(MAX([id]), 0)+1, 'aN', 'bN' FROM [t1];
COMMIT;
SET XACT_ABORT OFF;

What I want to know is how do you use ROLLBACK in case the transaction above fails?

PS. I basically need to revert the database to what it used to be in case of any error in that statement above.

EDIT: Updated with the SET XACT_ABORT ON; statement suggested below. Is it how it's supposed to look?

Answer 1

You need to set SET XACT_ABORT ON if you want the transaction to fully rollback on an error.

With this set to ON, if any of the statements fails, the transaction will rollback. You do not need to call ROLLBACK for that to happen.

Under this condition, the moment you use a BEGIN TRANSACTION, every statement will be part of that transaction, meaning they will all either work or all fail. If there is an error before the COMMIT, the transaction will rollback and you database will be at the same state it was as before BEGIN TRANSACTION (assuming no other clients are changing the database at the same time).

See the documentation for XACT_ABORT.

Answer 2

@Oded has covered an good approach to handle your rollback automatically. For completeness, I'll give you another method to handle that situation explicitly using try catch.

BEGIN TRANSACTION;

BEGIN TRY
    DELETE FROM [t1] WHERE [id]>10;
    INSERT INTO [t1] ([id], [v2], [v3]) SELECT COALESCE(MAX([id]), 0)+1, 'a1', 'b1' FROM [t1];
    INSERT INTO [t1] ([id], [v2], [v3]) SELECT COALESCE(MAX([id]), 0)+1, 'a2', 'b2' FROM [t1];
    --and so on, I may have up to 100 of these inserts
    INSERT INTO [t1] ([id], [v2], [v3]) SELECT COALESCE(MAX([id]), 0)+1, 'aN', 'bN' FROM [t1];
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO