December 1, 2010 at 2:49 am
Hi all
I recently wrote a transaction using @@error to check that there was no errors before committing a bunch of delete statements.
What I found was that the transaction committed even though one of the statements failed due to a foreign key constraint.
How can I write my transaction such that the transaction does not commit in such a situation?
Many thanks
December 1, 2010 at 4:46 am
Post code.
You probably didn't check @@Error correctly. It needs to be checked after every statement. Since you're on SQL 2005, rather use TRY ... CATCH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 1, 2010 at 4:50 am
Problem with @@error is that it catches the LAST error. So say you execute 3 deletes and the first fails. If you after the deletes check @@error its going to say 0 because the last delete was successful. So you have to check it after each statement. I have done a very simple example below. Both with @@error and since you posted in a SQL2005 forums i also added version that uses the new "try except", which simplifies it. However be warned that the "try except" doesnt catch all errors (check BOL for exceptions), but if its just deletes it should be fine.
set nocount on
/*create some tables to test against*/
create table test (i integer primary key, v varchar(32))
create table test2 (i integer, i2 integer primary key (i, i2))
alter table test2 add constraint test2_test foreign key (i2) references test (i)
go
/*add some data*/
insert into test (i, v) values (1, 'hi')
insert into test (i, v) values (2, 'hi')
insert into test2 (i, i2) values (1, 1)
go
/*the code*/
/*version with @@error*/
begin tran
declare @err integer
declare @rollback bit
select @err = 0, @rollback = 0
delete from test where i = 2
set @err = @@error
if @err <> 0 set @rollback = 1
delete from test where i = 1
set @err = @@error
if @err <> 0 set @rollback = 1
if @rollback = 1 rollback tran
else commit tran
go
/*version with try except*/
begin tran
declare @rollback bit
select @rollback = 0
BEGIN TRY
delete from test where i = 2
delete from test where i = 1
commit tran
END TRY
BEGIN CATCH
set @rollback = 1
rollback tran
END CATCH
go
/*see the result*/
select * from test
go
/*cleanup*/
drop table test2
drop table test
set nocount off
/T
December 2, 2010 at 6:10 am
Ahh I see well thanks for clearing that up for me 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply