Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Begin trans with Inserts

Author  Topic 

byka
Starting Member

18 Posts

Posted - 2014-08-12 : 12:36:31
Could you help me with writing SQL scripts with insert trans. If one of the inserts failed rollback all previous inserts.
Here what I come up with but I don't think it will work.
SET NOCOUNT ON;

BEGIN TRANSACTION;
BEGIN TRY
Insert 1
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

END CATCH

BEGIN TRY
Insert 2
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

END CATCH
BEGIN TRY
Insert 3
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;

END CATCH



IF @@TRANCOUNT > 0

COMMIT TRANSACTION

byka

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-13 : 08:31:06
You can use the below to roll back any failure in the tran(s).
SET XACT_ABORT ON;
SET XACT_ABORT OFF;

SET XACT_ABORT ON;
BEGIN TRAN
Code...
COMMIT TRAN
SET XACT_ABORT OFF;

We are the creators of our own reality!
Go to Top of Page
   

- Advertisement -