Commit Work and rollback work
-
Hello. Any one have used COMMIT WORK and ROLLBACK WORK? The idea is to process some inserts in quite a few tables, if error is more than zero rollback the whole script. I tried several options, but none seems to work. The insert statement works fine. We already test it and it is fine, but we are concern of the rollback process to get back to the beginning of the script and not commit any inserts, which it does not do. It inserts to the point of getting an error (data related or otherwise)
Any help is appreciated!! Should be something like
BEGIN TRANSACTION
insert into ... blah blah blah blah
blah blah blah
IF @@ERROR <> 0 ROLLBACK WORK
IF @@ERROR = 0
COMMIT WORK
Thank you!!
-
Try changing ROLLBACK WORK and COMMIT WORK to ROLLBACK TRANSACTION and COMMIT TRANSACTION
-
Well COMMIT WORK and ROLLBACK WORK works similar to COMMIT TRANSACTION and ROLLBACK TRANSACTION, but if you are using multiple BEGIN TRANSACTIONS in your SP the ROLLBACK work will rollback the Outermost BEGIN TRANSACTION code. And also check where exactly you are checking for the @@ERROR .
Ofcourse another major difference being COMMIT TRANSACTION and ROLLBACK TRANSACTION accept a user defined TRANSACTION NAME which [WORK] doesnot.
Thanks
Prasad Bhogadi
www.inforaise.com -
Also be mindful that @@ERROR is reset after each statement is executed. From looking at your pseudo-code, this may be your problem
BEGIN TRANSACTION
insert into ... blah blah blah blah
blah blah blah
IF @@ERROR <> 0 ROLLBACK WORK - the value of @@ERROR here is only for the last "blah" statement to occur, not for all of them
IF @@ERROR = 0 - the value here is likely to be zero because the last statement executed was just a read of @@ERROR
COMMIT WORK
You'll want to move @@ERROR into a local var after each statement, and read it later - like this:
BEGIN TRANS
insert blah
select @ins_error = @@Error
insert blah2
select @ins2_error = @@Error
if @ins_error = 0 and @ins2_error = 0
COMMIT TRANS
else
ROLLBACK
keith -
Great!! Thank you all. I think we figure out at the same time you guys were responding. Thank you anyway. I run it with the @@error for each statement in the sp and it seems to work. The developers will run it tonight the the big job they designed and hopefully it will work. Thank again
-
-- example
if @ins_error1 = 0 and ins_error2=0
begin
commit TRANSACTION
print 'good commit both tables should be updated'
select @ins_error1
select @ins_error2
end
else
begin
rollback TRANSACTION
print 'no commit - ROLLBACK'
select @ins_error1
select @ins_error2
end
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply