Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

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



Application Security Testing: An Integral Part of DevOps

Best Way to Delete Master Detail Records

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);
  with TableDetail do
      while not EOF do
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?

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:

WHERE D.DetailKey = MasterKey
In TQuery code it should look something like this:
procedure DeleteChildren(TableName, KeyField, KeyValue : String);
  qry := TQuery.Create(Application);
  with qry do begin
    SQL.Add('DELETE FROM "' + TableName + '" D');
    SQL.Add('WHERE (D."' + KeyField + '" = "' + KeyValue + '")');
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.



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