Delphi 10 -how to delete detail records automatically when delete master?

1
votes

I'm using Delphi 10 with Firedac , I have with two tables in a Master-Detail configuration , A(Document) and B(DocDetail) . In my Form I have two DBgrids, linked to each Datasource respectively. I want a configuration, if possible, that delete all records items in table DocDetail when I delete its corresponding Master record in the master DBgrid. Is there any configuration in the table FDTable component that does such action ? Or there any other way to do delete master - delete detail in Delphi side ? (I know that it is possible to do it in database side through a constraint delete cascade). Thanks for your help.

1
delphifiredac
Why don't you do it in the database side? - Ilyes
Well, yes it's possible. - Ilyes
Wait... Your comment to the answer by @MarkElder says you're using Oracle. Why aren't you doing this on the server instead of manually in code? You should be talking to your DBA. - Ken White
Ken, my major goal at this moment was to check if Delphi has the ability to do that and see if such method could give any advantage over doing it in database side, for instance providing a user alert before delete ! - JRG

1 Answers

3
votes

The properties you are looking for on the TFDQuery component are FetchOptions.DetailCascade and/or FetchOptions.DetailServerCascade

From the help on DetailServerCascade

When DetailServerCascade is False, then FireDAC posts client-side cascading changes to the database. The client-side cascading changes are performed when DetailCascade is True. So DetailServerCascade should be used together with DetailCascade.

If you are using CachedUpdates you may also need a TFDSchemaAdapter component. This CentralizedCachedUpdates Sample page list all of the steps need to setup the components when using cached updates.

I am using this on one form with good results. It was a little bit picky to get it all setup correctly. Basically every DataSet involved in the update needs to point to one common TFDSchemaAdapter component. Then any Master datasets need to have their FetchOptions.DetailCascade set to true to ensure that the rows are correctly deleted from the child datasets