locked
Force related rows into same changeset RRS feed

  • Question

  • Having an issue with sync timing. When the child table record is modified, it executes a trigger that updates a field on the parent table. The original assumption was that this would cause the parent and child records to both be included in the same changeset. However, if the user starts a sync while the trigger is updating the parent, then only the child record is brought down and therefore throws a foreign key violation error. The parent record is seemingly in the next changeset.

    Is there a way to ensure/force a parent record to be brought down at the same time as to avoid the foreign key errors?

    Thursday, April 15, 2010 7:38 PM

Answers

All replies

  • can you not do a join between the child and the parent table such that your selectincremental queries only returns child rows whose parent row has been updated already?

    then if you dont mind modifying how to set the last received anchor on the child table, you can set the last received anchor to the max timestamp on the child table.

    • Edited by JuneT Thursday, April 15, 2010 11:40 PM
    Thursday, April 15, 2010 11:22 PM
  • Thank you for the reply JuneT.

    I could join to the paretnt table, but here is my concern:

    If the change to child row 1 is in change set A, and the change to parent row 1 is going to be in change set B ("going to be" because of the potential latency of the trigger), when the user performs the sync (before the parent row is updated but after the child row is updated), with the proposed join in place, the child row would not be synced down (i.e. it would be filtered out). On the next sync, I would assume that the parent row would come down, but would the child? Since we have moved past its anchor point (with the change to the parent row)?

    Thursday, April 15, 2010 11:30 PM
  • hi dennis,

    pls see updated post... i got an error saving my first reply to you and when it did save, it was missing the 2nd line...

    Thursday, April 15, 2010 11:41 PM
  • You could disable the trigger while writing the child record (for example, by sending it a some kind of a special value in one of the fields if possible) and do a second-pass sync for parent row columns updated by the trigger. See here for related information - although it's dealing with an entirely different problem, the solution may be useful to you:

    http://www.8bit.rs/blog/index.php/2009/12/replicating-self-referencing-tables-and-circular-foreign-keys-with-microsoft-sync-framework/

    Friday, April 16, 2010 2:38 PM
  • Thank you Boris and JuneT.

    I dug into this a little bit more and ran a SQL Profiler trace on the mobile db. Seems that the FK error is because of how inserts and updates are being applied. So, I started a different thread with more details.

    The new thread can be accessed here: http://social.microsoft.com/Forums/en/syncdevdiscussions/thread/caa9b7b8-8f8d-4f3b-b45d-0df3cd9c7b94

    • Marked as answer by JuneT Thursday, July 8, 2010 1:41 PM
    Friday, April 16, 2010 2:49 PM