Rollbck Transaction - SQL Server Forums

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

Rollbck Transaction

Author

Topic

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2012-05-18 : 07:00:39

I have an Insert script which insert value into two table. I need to put the entire insert script under a single transaction and if the the script fails to insert data in any of the table then the whole transaction should be rollbacked.

Can any one please help.
I am trying with the below:

Begin tran
Begin
Insert into A (a) values (1)
Insert into B (b) values (2)
Insert into B (b) values (3) --suppose fails in this step then the entire traction should be rolled back
Insert into B (b) values (4)
Insert into B (b) values (5)
END
IF (@@ERROR<>0)
Rollback Tran

Else
Commit Tran

But did not work. :(

kalyan Ashis Dey

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2012-05-18 : 07:08:30

I understand one thing now @@ERROR always return the status of the last insert statement. So will it be work if i put the entire thing under a try catch block?

kalyan Ashis Dey

kalyan.cse05
Yak Posting Veteran

74 Posts

Posted - 2012-05-18 : 07:58:23

Got my answer :)
need to put the whole thing in try block and rollback section in catch block. i.e:
begin try
Begin tran
Begin
Insert into A (a) values (1)
Insert into B (b) values (2)
Insert into B (b) values (3) --suppose fails in this step then the entire traction should be rolled back
Insert into B (b) values (4)
Insert into B (b) values (5)
commit tran
END
end try
begin catch
Rollback Tran
end catch

kalyan Ashis Dey

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-18 : 10:15:30

Excellent! You answered your own question! Best way to learn, and saved me typing it all out too

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-19 : 06:30:06

I tried and It is working fine.It rollback all the records..

Begin tran
Begin

Insert into B (Val) values (2)
Insert into B (Val) values ('vava') --suppose fails in this step then the entire traction should be rolled back
Insert into B (Val) values (4)
Insert into B (Val) values (5)
END
IF (@@ERROR<>0)
Rollback Tran

Else
Commit Tran

Vijay is here to learn something from you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources