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:
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!procedure TForm1.TableMasterBeforeDelete(DataSet: TDataSet);begin with TableDetail do begin DisableControls; try First; while not EOF do Delete finally EnableControls; end; end;end;
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:
In TQuery code it should look something like this:DELETE FROM “MyDetail.DB” DWHERE D.DetailKey = MasterKey
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.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;
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.