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.
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 TRANSACTIONbyka |
|
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 TRANCode...COMMIT TRANSET XACT_ABORT OFF;We are the creators of our own reality! |
|
|
|
|
|