transaction question

[Alexander Korovyev]

Hello!

It seems like I have some misunderstandin g about the way transactions
work and would appreceate your help.

I want to delete records from 2 tables. Some records may not be
deleted due to reference constraints in which case no effect must take
place (i.e. no records must be deleted).

The following SQL code was supposed to solve my problem but it
doesn't..

BEGIN TRANSACTION
DELETE A WHERE ID=1
DELETE B WHERE ID=1
COMMIT

It still deletes A records if records in table B cannot be deleted.
What do I miss?

[Simon Hayes]

Re: transaction question


"Alexander Korovyev" <korovyev@rambl er.ru> wrote in message
news:26c82787.0 501050430.68578 3c4@posting.goo gle.com...[color=blue]
> Hello!
>
> It seems like I have some misunderstandin g about the way transactions
> work and would appreceate your help.
>
> I want to delete records from 2 tables. Some records may not be
> deleted due to reference constraints in which case no effect must take
> place (i.e. no records must be deleted).
>
> The following SQL code was supposed to solve my problem but it
> doesn't..
>
> BEGIN TRANSACTION
> DELETE A WHERE ID=1
> DELETE B WHERE ID=1
> COMMIT
>
> It still deletes A records if records in table B cannot be deleted.
> What do I miss?[/color]

You're committing the transaction every time - you need some error handling
to ROLLBACK the transaction if one of the DELETEs fails. See the code below,
which hopefully should give you the basic idea; you can check out these
links for more detailed information:

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

Simon

create proc dbo.DeleteRows
as
begin
delete from dbo.a where id = 1
if @@error <> 0
begin
/* Error, so rollback and quit the proc */
rollback
return
end

delete from dbo.b where id = 1
if @@error <> 0
begin
/* Error, so rollback and quit the proc */
rollback
return
end

/* No errors, so commit */
commit
end

[Gang He [MSFT]]

Re: transaction question

The second delete will throw an exception and fail. But the exception is
not severe enough to abort the transaction automatically, thus you can't
execute the "commit" commmand unconditionally . You need to use @@error to
check whether the delete statement hit exception then either commit or
rollback the transaction.

--
Gang He
Software Design Engineer
Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.
"Alexander Korovyev" <korovyev@rambl er.ru> wrote in message
news:26c82787.0 501050430.68578 3c4@posting.goo gle.com...[color=blue]
> Hello!
>
> It seems like I have some misunderstandin g about the way transactions
> work and would appreceate your help.
>
> I want to delete records from 2 tables. Some records may not be
> deleted due to reference constraints in which case no effect must take
> place (i.e. no records must be deleted).
>
> The following SQL code was supposed to solve my problem but it
> doesn't..
>
> BEGIN TRANSACTION
> DELETE A WHERE ID=1
> DELETE B WHERE ID=1
> COMMIT
>
> It still deletes A records if records in table B cannot be deleted.
> What do I miss?[/color]