Solved

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

Posted on 2014-03-05
2
316 Views
Last Modified: 2014-03-15
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
Comment
Question by:jazz__man
2 Comments
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 250 total points
ID: 39905859
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 250 total points
ID: 39908125
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now