CODEWITHSUNDEEP
sqlsql-serverrollback
ahmd0
ahmd0

Reputation: 17293

Any ideas for ROLLBACK TRANSACTION on this SQL statement

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?

Upvotes: 3

Views: 1036

Answers (5)

Asraf ali
Asraf ali

Reputation: 1

You can try the following method:

BEGIN TRY
   BEGIN TRANSACTION 
     DELETE FROM tablename WHERE id>1
     --- set of your querys 
    COMMIT
END TRY

BEGIN CATCH 
    IF @@TRANCOUNT > 0  
       ROLLBACK 
END CATCH

Upvotes: 0

Code Magician
Code Magician

Reputation: 23972

@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

Upvotes: 4

marc_s
marc_s

Reputation: 754518

You didn't mention your version of SQL Server - but starting with the 2005 version, you can use BEGIN TRY... END TRY BEGIN CATCH... END CATCH style exception handling.

I typically wrap my statement blocks in a transaction/try-catch block skeleton something like this:

BEGIN TRANSACTION
BEGIN TRY

    -- put your statements to be executed *HERE*    

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage

    ROLLBACK TRANSACTION
END CATCH

That block tries to execute my statements - if it succeeds, the transaction is committed, if an exception happens, the execution jumps into the CATCH block, prints out detailed error info, and rolls back the transaction

Upvotes: 1

Oded
Oded

Reputation: 499002

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.

Upvotes: 4

user978122
user978122

Reputation: 5761

http://www.codeproject.com/KB/database/sqlservertransactions.aspx

The above may give you an idea.

Upvotes: 1

Related Questions