Best Way to Delete Master Detail Records

Best Way to Delete Master Detail Records

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.

Share the Post:
XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes

ransomware cyber attack

Why Is Ransomware Such a Major Threat?

One of the most significant cyber threats faced by modern organizations is a ransomware attack. Ransomware attacks have grown in both sophistication and frequency over the past few years, forcing

data dictionary

Tools You Need to Make a Data Dictionary

Data dictionaries are crucial for organizations of all sizes that deal with large amounts of data. they are centralized repositories of all the data in organizations, including metadata such as