Board index » delphi » Master-Detail Problem

Master-Detail Problem


2004-04-27 01:26:46 PM
delphi74
Hi,
I want to know whether there any option in ADODataset, when a master records are deleted the detail Records are automatically delete itself(It should also support updates too). If so where can I set it or how can I do it?
I'm completely stuck with this. I have 4 tables, two of the tables is for entry purposes(assume that the tables as table1 & table2), and the next 2 tables takes the role of History table(assume that these tables as table3 & table4). Table1 is the master, and table2 as it is Detail. Also is the same for the 2 History tables(table3 is the master and table4 as it is detail table).
After Inserting records to table1 & table2 the user clicks a button to send the records it is History tables. If everything is successfully exported then I want to delete the inserted records in table1 & table2.
I used a ADOQuery1 for first delete the Detail and Execute it. Then Used a another ADOQuery2 for delete the master and execute it in Entry Tables. I wrote the following code in a Transaction as:
Try
ADOConnection.BeginTrans;
ADOQuery1.ExecSQL;
ADOQuery2.ExecSQL;
table1.Refresh;
table2.refresh;
ADOConnection.CommitTrans;
Except
on E:Exception do
ADOConnection.RollbackTrans;
End;
However received the following errors:
"Project ShareList.Exe raised exception class EOleException with message 'Key value for this row was changed or deleted at the data store. The local row is now deleted'. Process stopped. Use
step or run to continue"
and another one as:
"Project ShareList.Exe raised exception class EOleException with message 'Row handle referred to a deleted row or a row marked for deletion'. Process stopped or Run to continue"
Also it rollbacks the data and not gets deleted in entry tables.
How can I do this process as an error free process.
Awaiting somebodys answer.
Thanx,
nazeer.
 
 

Re:Master-Detail Problem

This is just a guess, based on the info you gave, ...
query objects are really expecting to return recordsets. I assume that the
query you have contains some kind of "delete from xxx where key = somevalue"
logic in them. If so, try using a tADOCommand for this type of logic. OR,
you can actually put the delete statements in your connection objcect. ...
adoConnection.begintrans
try
adoConnection.execute('delete from someDetailTable where ...')
// then if this does not raise an exception ...
adoConnection.execute('delete from SomeMasterTable where ...') //
then if this does not raise an exception ....
.. next table deletes ..
adoConnection.CommitTrans
except
adoConnection.RollBackTrans
end;
hope this helps
PS ...dont try to use the execute method of a connection object to return
record sets
 

Re:Master-Detail Problem

"Hifni" <XXXX@XXXXX.COM>writes
Quote
Hi,

I want to know whether there any option in ADODataset,
when a master records are deleted the detail Records are
automatically delete itself [SNIP]
This can be handled by a referential integrity constraint with cascading
deletes. An example field definition would be "customer_id INT NOT NULL
REFERENCES customers(id) ON DELETE CASCADE".
Quote
(It should also support updates too). If so where can I set
it or how can I do it?
This can be done with an AFTER INSERT or AFTER UPDATE trigger.
Neither of these options will require any code to be added to your app!
Quote
I'm completely stuck with this. I have 4 tables, two of the tables
is for entry purposes(assume that the tables as table1 & table2),
and the next 2 tables takes the role of History table(assume that
these tables as table3 & table4). Table1 is the master, and
table2 as it is Detail. Also is the same for the 2 History tables
(table3 is the master and table4 as it is detail table).

After Inserting records to table1 & table2 the user clicks a
button to send the records it is History tables. If everything is
successfully exported then I want to delete the inserted
records in table1 & table2.
I'd probably do this with a BEFORE DELETE trigger on table1. Have it
automatically copy the matching records from table1->3 and table2->4 and
then allow the original in table1 to be deleted if everything goes right
(the table2 records are automatically deleted by the CASCADE, remember?).
All you have to do in your app is delete a record from table1 and the
database takes care of the history files.
Quote
I used a ADOQuery1 for first delete the Detail and Execute it.
Then Used a another ADOQuery2 for delete the master and
execute it in Entry Tables. [SNIP everything else]
The rest is hard to interpret without seeing the actual SQL. If you did the
deletes before copying to history (like you said above) then an error about
non-existent records seems perfectly logical.
--
Ray Marron