Solved

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

Posted on 2014-03-05
2
315 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:
ScottPletcher 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

759 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

18 Experts available now in Live!

Get 1:1 Help Now