Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Relational Databases
Expertise: Beginner
Mar 28, 1997

Best Way to Delete Master Detail Records

Question:
As you know, when you create .db tables with referential integrity (i.e. Paradox or Desktop), this makes detail records that depend on the master. When you want to delete records in the master table, this can be performed only when you have already deleted records in the detail table. This means that you must perform (under BeforeDelete event of the master table) code that will erase detail records.

It looks very easy:

procedure TForm1.TableMasterBeforeDelete(DataSet: TDataSet);
begin
  with TableDetail do
  begin
    DisableControls;
    try 
      First;
      while not EOF do
        Delete
    finally 
      EnableControls;
    end; 
  end;
end;
 
But this code ensures deleting the master record regardless of the success of the detail delete operation. For example, if any of the detail records are locked, they aren't deleted, so orphan records are made. Even worse, if the whole table is locked, no detail records are deleted!

This can be blocked by a record lock exception, which terminates the BeforeDelete event and leaves DBGrid without visual control (no refresh).

If I solve this problem by making locks, this action must check all the possible locks on the detail records made. If not, erasing is not performed correctly!

As far as I know for the BDE, when you lock many detail records the program may spend a lot of resources, so in some situations it can hang up. This means that you have to make a certain number of records that will present the maximum of possible locks. After that, if more locking is needed, you have to lock the whole table, which isn't easy work for frequently used tables on a network.

Things are even worse when you have deep relations (i.e. 1:m:m) with proper referential integrity for tables (Customer->Order, Order->Items).

The Code mentioned before doesn't work because when you put them in the Customers and Orders BeforeDelete event handler, the result is one deletion per Order record and all of their items. This means that you have to start the delete several times. I'm afraid that the master-detail copy and the update procedure are very similar. Is there a typical solution for this?

Answer:
Your best bet is to set both the master and detail tables to Active := False to temporarily close them. Then, use a query to delete the detail records:

DELETE FROM "MyDetail.DB" D
WHERE D.DetailKey = MasterKey
 
In TQuery code it should look something like this:
procedure DeleteChildren(TableName, KeyField, KeyValue : String);
begin
  qry := TQuery.Create(Application);
  with qry do begin
    SQL.Clear;
    SQL.Add('DELETE FROM "' + TableName + '" D');
    SQL.Add('WHERE (D."' + KeyField + '" = "' + KeyValue + '")');
    try
      ExecSQL;
    finally
      Free;
    end;
  end;
end;
 
Mind you, this is off the top of my head, so I'll leave it up to you to fix the syntactical errors. However, this is the way I always delete children.

The premise is that if you want to delete the master records with children attached, you need only delete the children whose keys match the master's key.

The problem here, which I admit readily, is that RI is not being obeyed, so with deep relations it can present a problem. Also, doing this type of query requires an exclusive lock, which you might not get. In that case, what I do is start a timer that will cycle so that as soon as I can get exclusive access to all the tables, the deletes will occur.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date