How do I do a sql transaction where it rolls back if something fails?

jazz__man
jazz__man used Ask the Experts™
on
Hi,

I have a large sql query running that inserts records into multiple tables but what I want to do is have the whole thing roll back if an insert fails for some reason.


What would be the correct syntax for doing this transaction?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
HuaMin ChenProblem resolver
Commented:
See this example
USE AdventureWorks2012;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
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;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Open in new window


This will also work for Update and Insert.
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
You must explicitly start a transaction, put all INSERTs within that transaction, and then either commit or rollback the trans.

BEGIN TRY
BEGIN TRANSACTION

INSERT INTO dbo.table1 ( ... )
SELECT ...
FROM ...

INSERT INTO dbo.table2 ( ... )
SELECT ...
FROM ...

...

COMMIT TRANSACTION
END TRY

BEGIN CATCH
IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION
...other error code here, including using ERROR_* functions to get error info...
END CATCH

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial