469,112 Members | 2,030 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,112 developers. It's quick & easy.

transaction question

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?
Jul 23 '05 #1
2 1105

"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
Jul 23 '05 #2
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" <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?

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Avanish Pandey | last post: by
8 posts views Thread by Alex | last post: by
12 posts views Thread by John Sidney-Woollett | last post: by
15 posts views Thread by Zeng | last post: by
1 post views Thread by REB | last post: by
14 posts views Thread by Jim Michaels | last post: by
10 posts views Thread by Lit | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.