Multiple deletes and updates in TMSQuery fails on ApplyUpdates: PK error.

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
dougcl

Multiple deletes and updates in TMSQuery fails on ApplyUpdates: PK error.

Post by dougcl » Thu 08 Sep 2005 17:52

Hello,
I am getting a primary key violation when I call ApplyUpdates on a TMSQuery object that has two deletes pending. No problem if only one delete is pending. I have an OnAfterDelete event handler on the TMSQuery object that re-orders the primary key values following a delete. For example:
PK Value
1 A
2 B
3 C
4 D

User deletes record 3, and OnAfterDelete changes the value of the PK to remove the gap:
PK Value
1 A
2 B
3 D

Then I call apply updates. Everything is fine if the user only deletes one record (as shown above). If the user deletes more than one record though, like this:

PK Value
1 A
2 D

I get a PK violation on duplicate insert when I call ApplyUpdates. Error goes away if I delete the last record (2 D in the above example).

Assuming that I am assigning the PK's correctly, and that the cached version of the dataset is ok (no PK duplications) should TMSQuery object be able to handle this? Is there some limitation to TMSQuery object that prevents this sequence of changes?

SQL select query on TMSQuery object in this case is
select * from table order by PK. Delete, Insert, Refresh, etc, are left blank.

Not sure why the TMSQuery object is (apparently) trying to carry out an insert. It should just delete, update, delete, update.

Thanks,
Doug

Ikar
Posts: 1693
Joined: Thu 28 Oct 2004 13:56

Post by Ikar » Fri 09 Sep 2005 14:20

TMSQuery considers that every record is independent and does not provide unambiguous order of update query execution. Try not to change PK values or to move this functionality from primary key.
Also you can move this functionality to server side (with triggers)

Post Reply