By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

transaction question

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a

"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

P: n/a
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.