Deleting a master record after updating a child record that has been unlinked from the master

Data Abstract
1

Should it be possible to delete a master record that has child records attached but that have been unlinked?

This fails for us with the latest remobjects

In code we do something like

var
bes : IBestelling;
bit:IBestelItems;
begin
if Supports(FBestellingTable, IBestelling, bes) and
Supports(FBestellingTable.BestelItems, IBestelItems, bit) then
…
Foreach FBestellingTable.BestelItems record
bit.Edit();
bit.bit_bes_primkey := Fbes_primkeyDest; <- this unlinks FBestellingTable from FBestellingTable.BestelItems
bit.Post();
bes.Delete();

What happens in SQL profiler is

  1. DELETE FBestellingTable record
  2. DELETE ALL FBestellingTable.BestelItems records
  3. UPDATE ALL FBestellingTable.BestelItems <- THEY HAVE JUST BEEN DELETED
    See below for logs

The delete of the child table is triggered by a business server rule
procedure TBestellingServerRules.AfterProcessChange( Sender: TDABusinessProcessor; aChange: TDADeltaChange; Processed: Boolean; var CanRemoveFromDelta: Boolean);

DA does not realize the UPDATE should come first (or delete is not necessary)
Can this behavior be altered? Has the behavior of DA/RO changed? We did not notice this issue earlier.

declare @p1 int
set @p1=39
exec sp_prepare @p1 output,N’@P1 int OUTPUT’,N’DELETE FROM Bestelling
WHERE
(“bes_Primkey”=@P1)’,1
select @p1
exec sp_execute 39,100031153

exec sp_executesql N’Delete From bestelItems where bit_bes_Primkey = @P1’,N’@P1 int’,100031153

declare @p1 int
set @p1=56
exec sp_prepare @p1 output,N’@P1 datetime OUTPUT,@P2 int OUTPUT,@P3 int OUTPUT,@P4 int OUTPUT’,N’UPDATE BestelItems SET
“bit_DateChanged”= @P1,
“bit_bes_Primkey”= @P2,
“bit_bes_primkey_Before_Merge”= @P3
WHERE
(“bit_Primkey”=@P4)’,1
select @p1
exec sp_execute 56,‘2020-03-20 12:30:24.627’,100031066,100031153,100468817
exec sp_execute 56,‘2020-03-20 12:31:05.457’,100031066,100031153,100468816
exec sp_execute 56,‘2020-03-20 12:30:52.730’,100031066,100031153,100468798

2

Hi,

You can specify Update Rules in Schema Modeller.

Also you can play with table.DetailOptions and table.MasterOptions properties.

you may need to uncheck moCascadeDelete in mastertable.MasterOptions and dtCascadeDelete in detailtable.DetailOptions

3

Thanks,
Can you tell me how exactly do we can disable the bussiness rule TBestellingServerRules.AfterProcessChange?

4

Hi,

You can remove relationship between master and detail tables.

by other hand, you can specify Update rules as

  • detail table: Insert, Update
  • detail table: Delete
  • master table: Insert, Update, Delete
5

Hello Evgeny,

FYI: I need the child table update to be executed and I wish to do this atomically with the master record delete.

  1. I do not want the business rule which deletes the child records too soon to execute.
    or alternatively
  2. I want the business rule which deletes the child records to execute after the child record updates.

Is that possible?

6

hi,

have you tried this solution?

7

Hi Evgeny,

I don’ quite see how I can apply these rules to obtain what I want.
I need the relationship between master and detail.

FYI: A solution is to use non atomic applies
So when I do
FBestellingTable.BestelItems.ApplyUpdates();
FBestellingTable.ApplyUpdates();
instead of just
FBestellingTable.ApplyUpdates();
everything gets executed in the right order, being:

  1. Update child records
  2. Delete master record
  3. Delete all child records attached to the master (but there are none anymore which gets executed without an error)

I have not tried disabling moCascadeDelete and dtCascadeDelete`.
Does this impact the call to TBestellingServerRules.AfterProcessChange? I assume not.

8

Hi,

this one performs while (RecordCount > 0) do Delete; on client-side for detail table when master is deleted.

if this solution works for you, you can use it:

FBestellingTable.BestelItems.ApplyUpdates();
FBestellingTable.ApplyUpdates();