Deletes and inserts in same update = primary key violations

Data Abstract
1

Hi,

I have a volatile child table that needs all the child records deleting and then re-inserting a new batch, some of which may be the same as those that were deleted. I tried the following code:

// delete all existing keywords
while tbl.recordcount > 0 do tbl.delete;
// (A)
//  now insert the new keywords
for i := 0 to KeywordList.Count-1 do
begin
  tbl.insert;
  tbl.ParentID := ID;
  tbl.Keyword := KeywordList.strings[i];#
  tbl.Post;
end;
tbl.ApplyUpdates;

The tbl is a TDAMemDataTable, the sql table itself has just the two fields ParentID & Keyword which together form the primary key.

I would’ve thought that the above code would delete the existing records then insert the new ones in a batch, but I get numerous Primary Key violation errors ?

If I add “tbl.ApplyUpdates” at (A) above, then the problem goes away.

Is this an RO/DA bug? If not, how to I delete and re-insert in a transaction?

Cheers

Delphi 10.1 Berlin
RO/DA 9.0.97.1245
SQL Server 2008 R2 using FireDAC

2

looks like you need to create two update rules for this table in schema like

  • delete only
  • insert and update only
3

Hi,

Thanks for the heads up.

However, from the documentation:

“Data Abstract will process the delta updates in the order in which they are received.”

So if I create a bunch of deletes to the delta, and then create a bunch of inserts - how is this then processed in a different order on the server? Seems to me that DA should retain the order of updates in the delta?

Cheers

4

yes, they should be processed in the order in which they are received, but probably something goes wrong.
can you confirm that update rules solve this issue?