Question:
As you know, when you create .db tables with referentialintegrity (i.e. Paradox or Desktop), this makes detail records that dependon 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 ofthe detail delete operation. For example, if any of the detail records are locked, they aren’t deleted, so orphan records are made. Even worse, ifthe whole table is locked, no detail records are deleted!

This can be blocked by a record lock exception, which terminatesthe 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 notperformed correctly!

As far as I know for the BDE, when you lockmany detail records the program may spend a lot of resources, so in somesituations it can hang up. This means that you have to make a certainnumber of records that will present the maximum of possible locks. Afterthat, if more locking is needed, you have to lock the whole table, which isn’teasy 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 detailrecords:

DELETE FROM “MyDetail.DB” DWHERE 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 fixthe syntactical errors. However, this is the way I always delete children.

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

The problem here, which I admit readily, is that RI is not being obeyed, sowith deep relations it can present a problem. Also, doing this type ofquery 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 getexclusive access to all the tables, the deletes will occur.