• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

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

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?
0
jazz__man
Asked:
jazz__man
2 Solutions
 
HuaMinChenBusiness AnalystCommented:
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.
0
 
Scott PletcherSenior DBACommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now