basic transaction willl not roll back? help

Question

Post reply

basic transaction willl not roll back? help

adam spencer

Hall of Fame

Points: 3926
More actions
January 28, 2006 at 8:56 pm

#90523

It only seems to rollback after the value in the record is already -0
for example if the balance in record is 500 and we subtract 1000, it does not rollback. but the next time when the balance is -500, it rolls back. can someone help and tell me what i am doing wrong.
thanx!

Declare @Balance Money
Begin transaction
   Update Table1
    set @Balance = Balance = Balance - 1000
    where AccNo = '13245669'
If @Balance => 0
    Begin
        Commit Transaction
   Else
    Begin
          Rollback Transaction
   End

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Why not simply do:

Update Table1

set Balance = Balance - 1000

where AccNo = '13245669'

and Balance >= 1000

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

HemaSunder Bugatha Right there with Babe Points: 717 More actions · Answer 2

You forgot to give and 'end ' at the end of the transaction.

There are three 'begin' statements but only two 'ends'

try this.....

declare @Balance money

begin transaction

......................

........................

if @Balance >=0

begin

commit transaction

end

else

rollback transaction

end

..hema

JeffB Hall of Fame Points: 3541 More actions · Answer 3

A "begin transaction" statement does not require an "end"

The following works but I agree that its best to do the update by qualifying the balance in the where clause.

create table table1 (Balance Money, AccNo varchar(20))

insert table1 values(500, '13245669')

Declare @Balance Money

Begin transaction

Update Table1

set @Balance = Balance = Balance - 1000

where AccNo = '13245669'

If @Balance >= 0

Begin

Commit Transaction

end

Else

Begin

Rollback Transaction

End

Manjunath Shankar SSC Journeyman Points: 84 More actions · Answer 4

the code

If @Balance => 0

.... should be written as if @Balance>=0. I think you have given it as @Balance=>0. This might be one of the reasons for the sp not to execute properly.

Manju