473,320 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 1288

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Avanish Pandey | last post by:
Hello All We have 3 differen services (in 3 different server) Service A,B,C . We want to implement distributed transaction when call methods of B and C from A. Is it possible? if yes then how? ...
8
by: Alex | last post by:
Hi, I have a test system that is setup the same as a production system and would like to frequently copy the database over. pg_dump takes a few hours and even sometimes hangs. Are there any...
12
by: John Sidney-Woollett | last post by:
I have to convert an java web application currently using an Oracle DB back end to one using a Postgres backend. In Oracle much of the application logic is abstracted away from the java...
15
by: Zeng | last post by:
Hi, The bigger my C# web-application gets, the more places I need to put in the tedious retrying block of code to make sure operations that can run into database deadlocks are re-run (retried)...
1
by: REB | last post by:
If I create a transaction object for rolling back a insert query in the on click event of a form button can the transaction be tracked across other functions? For example: private void...
2
by: msnews.microsoft.com | last post by:
Hello, I have the scenario. I m building an application either in asp.net or window application. This application is base on n-tier application model. Let us take example of Northwind Database in...
14
by: Jim Michaels | last post by:
mysql_query("START TRANSACTION", $link2); $q2=mysql_query("SELECT pictures.pid AS pid FROM pictures,counter WHERE pictures.pid>counter.pid LIMIT 1", $link2); if ($row2=mysql_fetch_assoc($q2)) {...
0
by: tony | last post by:
Hello! Below I have three methods it's SavePost, SavePostSetUpTableFlg and SavePostBlowStepFlg. I want to use transaction to keep the data synchronized. All or nothing must be executed. When...
3
by: sifrah | last post by:
Hi All, My SQL server transaction log is getting bigger every day and my HDD if running out of space. So i follow the MS KB about how to Shrinking the Transaction Log. After doing so the log is...
10
by: Lit | last post by:
Hi, using ADO.NET 2.0, VS.NET 2005, SQL2005 I need to execute several Stored Procedures under one transaction. Can I use just one command/transaction or have to use multiple ( then commit or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.