commit/rollback trans - 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

SQL Server 2000 Forums

Transact-SQL (2000)

commit/rollback trans

Author

Topic

rushdib
Yak Posting Veteran

93 Posts

Posted - 2003-12-17 : 12:57:58

Hi,
I have a procedure with two inserts

begin tran
insert ....

insert .....

if @@error <> 0
rollback tran
else
commit tran

If the 2nd insert fails i want to rollback the transactions on the first insert. How do I do that?
The current set up commits the 1st insert eventhough the 2nd insert fails.

Thanks,

Rushdi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-17 : 13:07:15

You need to check @@ERROR afte each data modification:



BEGIN TRAN

1st INSERT...

IF @@ERROR <> 0
  ROLLBACK TRAN
ELSE
  2nd INSERT...
    IF @@ERROR <> 0
       ROLLBACK TRAN
    ELSE
       COMMIT TRAN

Tara

krishnan_sn
Starting Member

14 Posts

Posted - 2003-12-19 : 02:35:26

hi,

after the begin tran statment try with begin statement
like...

begin tran
begin
insert ....

insert .....
end
if @@error <> 0
rollback tran
else
commit tran

try this may help u...

S.Navaneetha Krishnan

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2003-12-19 : 11:59:17

You should really do what Tara said. You don't want to get a whole string of transactions you have to roll back, when you could just rollback one. You will be locking all these tables while you're rolling back these transactions. This becomes more important with more complicated queries.

An idea for cleaner code might be:

BEGIN TRAN TRAN1

INSERT blah, blah, blah

IF @@ERROR <> 0
GOTO ERRORINSERT

INSERT blaher, blaher, blaher

IF @@ERROR <> 0
GOTO ERRORINSERT

COMMIT TRAN TRAN1
RETURN 0

ERRORINSERT:
ROLLBACK TRAN TRAN1
RAISERROR('THIS SUCKS',16,1)
RETURN 1

Or something similar depending on how incredible blah you want your code to be.

(grin)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-19 : 12:07:15

quote:
Originally posted by krishnan_sn

hi,

after the begin tran statment try with begin statement
like...

begin tran
begin
insert ....

insert .....
end
if @@error <> 0
rollback tran
else
commit tran

try this may help u...

S.Navaneetha Krishnan

You have to check @@ERROR after every data modification. If the first INSERT fails and the second INSERT succeeds, @@ERROR will show success. So your example is not correct.

Tara

rushdib
Yak Posting Veteran

93 Posts

Posted - 2003-12-19 : 12:30:05

Hi,
This process runs once a month by one user and I have to roll back all the transactions if one transaction fails as per request by the client.
So I am using the following logic. Let me know if this is a good idea

begin
begin tran
insert 1
if @error <> 0
begin
rollback tran
goto on_error
end
insert 2
if @error <> 0
begin
rollback tran
goto on_error
end
commit tran
one_error:
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-19 : 12:32:21

Yes that looks fine. I personally avoid labels (like your on_error:) though as it was drilled in my brain not to use them. Not using them will require more lines of code. But to each his/her own.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2003-12-19 : 12:59:14

It's preference. I like less lines if it does the same thing.

Actually though, I just like disagreeing with you. (grin)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

krishnan_sn
Starting Member

14 Posts

Posted - 2003-12-19 : 23:45:04

Thanks Tara,

I understood the concept behind it.
Instead of using goto statements why can't we have a Flag variable and if the sql statements retruns error the status of the flag is changed ...at the end based on the status of flag we can go for either comit tran or rollback tran... what's ur suggestion about it.

thanks

S.Navaneetha Krishnan

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-27 : 20:56:54

As long as you check @@ERROR after every data modification and then do conditional logic based on it, then you are fine. But you can't just check @@ERROR at the end of a batch of commands and expect it to work right.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-29 : 11:47:31

quote:
Originally posted by tduggan

Yes that looks fine. I personally avoid labels (like your on_error:) though as it was drilled in my brain not to use them. Not using them will require more lines of code. But to each his/her own.

Tara

So Tara, you repeat your exit routines at every check?

As long as you're 1 label to error handle and exit to, I think it's a good thing.

It get hammered in to not do it because a lot of times you get spagehtti code...

Strive for

1 BEGIN
1 ROLLBACK
1 COMMIT
1 END
1 Error Label
1 Exit Label (for house keeping)
n lines of fall though logic

OK, I guess a MOO is required right about now....

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30311&SearchTerms=Error:,,Exit:

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources