locked
Primary Key update RRS feed

  • Question

  • Is there anyway to handle primary key update? If I update the primary key values the linking between the tracking table and main table is breaking. Leaving the tracking table row orphan. Please advise.
    Friday, October 1, 2010 7:42 AM

Answers

  • Ok I updated the UPDATE trigger.

    1. Check if there is an update in PRIMARY KEY column

    2. If Primary key is modified  Used cursor to loop through the modification

    3. Check if tracking table already have the records with new primary key update if so mark it as not deleted and update it so that it goes in next sync and mark original one as deleted. (This is required since if you already have that record in tracking table with deleted mark, you will receive primary key constraint error on insert)

    4. If tracking table do not have record already insert a new record with new primary key value and mark the old one as deleted.

     

    This works perfect for me now!

     

    Thanks very much for all you help.

    • Marked as answer by Varde Thursday, October 7, 2010 9:20 AM
    Thursday, October 7, 2010 9:20 AM

All replies

  • try modifying the update trigger on the table you're synching to include updating the primary key on the tracking table.
    • Marked as answer by Varde Friday, October 1, 2010 11:27 AM
    • Unmarked as answer by Varde Friday, October 1, 2010 11:52 AM
    Friday, October 1, 2010 10:43 AM
  • Modifying is update trigger is not enough. I will have mark it as deleted in tracking table and then create a new row. This is required to delete record from client with old primary key and then insert a new records with new primary keys values. Thoughts?
    Friday, October 1, 2010 11:54 AM
  • use a new Guid column as the Primary key, have the modifiable one a a unique index..
    Friday, October 1, 2010 12:24 PM
  • I cannot alter the existing database schema.
    Friday, October 1, 2010 12:56 PM
  • Modifying is update trigger is not enough. I will have mark it as deleted in tracking table and then create a new row. This is required to delete record from client with old primary key and then insert a new records with new primary keys values. Thoughts?

    yes, that seems right.
    Friday, October 1, 2010 3:28 PM
  • I have composite primary key on tables, and the keys are updatable in the application hence I will have to edit the update trigger to keep the tracking table in sync with main table. 

    I have added following code in UPDATe trigger. However due to builk update it is failing.

     

    IF UPDATE(Key1)OR UPDATE(Key2)
        BEGIN
            UPDATE [sync].[Table_Tracking] SET
                Key1 = (SELECT TOP 1 Key1 FROM INSERTED),
                Key2 = (SELECT TOP 1 Key1 FROM INSERTED),

            WHERE
                Key1 = (SELECT TOP 1 Key2 FROM DELETED) AND
                Key2 = (SELECT TOP 1 Key2 FROM DELETED) AND            
        END

     

    This works if there is single row update but for multiple update it fails due to the SELECT TOP 1 syntax.

    I know this is not the sync framework related but somebody might have had the same issue and found and workaround? I don;t think I can use the CURSOR since my primary key values has been modify and i want to update the same in tracking table.

    Tuesday, October 5, 2010 9:23 AM
  • I used the the CURSOR and fixed the above issues.

    However trapped in other issue. I am using DownloadOnly sync with SqlSyncProvider.

    Any updates (on primary and I updated the primary key of tracking table using cursor) on the the server does not works well. It adds new record on client and leave the edited one as it is. I don't see any way to fix this?

    Please help.

     

    Tuesday, October 5, 2010 4:18 PM
  • can you try your previous thought of deleting the row on the tracking table and inserting a new row for the updated primary key?

    this should a delete and insert on the client side.

    Wednesday, October 6, 2010 2:47 PM
  • Ok I updated the UPDATE trigger.

    1. Check if there is an update in PRIMARY KEY column

    2. If Primary key is modified  Used cursor to loop through the modification

    3. Check if tracking table already have the records with new primary key update if so mark it as not deleted and update it so that it goes in next sync and mark original one as deleted. (This is required since if you already have that record in tracking table with deleted mark, you will receive primary key constraint error on insert)

    4. If tracking table do not have record already insert a new record with new primary key value and mark the old one as deleted.

     

    This works perfect for me now!

     

    Thanks very much for all you help.

    • Marked as answer by Varde Thursday, October 7, 2010 9:20 AM
    Thursday, October 7, 2010 9:20 AM