"Alexander Korovyev" <ko******@rambler.ru> wrote in message
news:26**************************@posting.google.c om...
Hello!
It seems like I have some misunderstanding 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?
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