locked
Cannot delete record. RRS feed

  • Question

  • Playing with demo application and trying to test delete-insert-delete scenario.

    delete orders where order_id = 2

    insert into orders (order_id, order_date) values(2, GetDate())

    delete orders where order_id = 2

    Got the following error:

    Msg 2627, Level 14, State 1, Procedure orders_delete_trigger, Line 3

    Violation of PRIMARY KEY constraint 'PK__orders_tombstone__0425A276'. Cannot insert duplicate key in object 'dbo.orders_tombstone'.

    The statement has been terminated.

    I

    Isn't it possible to delete the same PK twice?

    • Moved by Max Wang_1983 Friday, April 22, 2011 11:27 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, January 26, 2007 7:31 PM

Answers

  • Interesting questions.

    How to distinguish delete-insert-delete and insert-delete-insert?
    I am not sure how to answer this question without going into too many details. Basically, you keep metadata on base and tombstone tables. In the sample, I’ve added create_timestamp and update_timestamp columns in both tables. Assuming you can store multiple delete records in the tombstone table, you can easily distinguish between delete-insert-delete and insert-delete-insert

    Does framework controls the sequence of inserts and deletes using timestamp or it is up to me?
    No. ServerSyncProvider does not order changes according to timestamp before applying. It only applies deletes before inserts or updates. This sync framework replicates net changes only and thus ordering is not needed.

    Does framework optimizes something (i.e. delete-insert-delete = no operation)?
    delete-insert-delete = delete of the initial insert (second delete is no operation) 
    The optimization happens in the SelectIncremementalDeletesCommand on the SyncAdapter object. This command is typically written in a way to skip the rows that were created and deleted after the last sync timestamp and before the new sync begins. This explains why I’ve the create_timestamp column in the tombstone table.

     

    Friday, January 26, 2007 8:59 PM

All replies

  •  

    It is possible, it just a limitation in the demo application. If you look at the demo.sql you will see that it created a trigger on table for delete operation. The trigger inserts the row to the tombstone table. The trigger is very simple; here is the one for orders table:

     

    CREATE TRIGGER orders_delete_trigger on orders for delete

    as

                insert into pub..orders_tombstone (order_id, order_date, create_timestamp, update_originator_id) select order_id, order_date, create_timestamp, 0 from deleted

    go       

     

    As you might have noticed already, the trigger does not assume that a row might exist in the tombstone table already with the same PK therefore it fails with constraint violation. I would encourage you to update this trigger to do be more sophisticated and handle multiple deletes of same PK. In fact you can do all sort of things in the trigger based in your scenario, like auditing .. etc.

     

    A side note here, we store deletes in tombstone table such that when the client syncs with the server, the server can tell the client all the changes that took place since it last synced which include insets, updates (from the base table) and deletes (from the tombstone table)

     

     

    Thanks,

    Rafik

     

    Friday, January 26, 2007 7:56 PM
  • I see.

    The question here is:

    How to distinguish delete-insert-delete and insert-delete-insert?

    Does framework controls the sequence of inserts and deletes using timestamp or it is up to me?

    Does framework optimizes something (i.e. delete-insert-delete = no operation)?

    Friday, January 26, 2007 8:20 PM
  • Interesting questions.

    How to distinguish delete-insert-delete and insert-delete-insert?
    I am not sure how to answer this question without going into too many details. Basically, you keep metadata on base and tombstone tables. In the sample, I’ve added create_timestamp and update_timestamp columns in both tables. Assuming you can store multiple delete records in the tombstone table, you can easily distinguish between delete-insert-delete and insert-delete-insert

    Does framework controls the sequence of inserts and deletes using timestamp or it is up to me?
    No. ServerSyncProvider does not order changes according to timestamp before applying. It only applies deletes before inserts or updates. This sync framework replicates net changes only and thus ordering is not needed.

    Does framework optimizes something (i.e. delete-insert-delete = no operation)?
    delete-insert-delete = delete of the initial insert (second delete is no operation) 
    The optimization happens in the SelectIncremementalDeletesCommand on the SyncAdapter object. This command is typically written in a way to skip the rows that were created and deleted after the last sync timestamp and before the new sync begins. This explains why I’ve the create_timestamp column in the tombstone table.

     

    Friday, January 26, 2007 8:59 PM
  • Thank You for your detailed answers.

    Friday, January 26, 2007 9:18 PM