none
is it possible to update the column value within the same sync transaction (Client Upload Server Update) RRS feed

  • Question

  • Hello all,

     

    Let say, I have a table <Order> with 2 columns (OrderId and OrderDate). In the original design, OrderId is the surrogate (which is still somewhat meaningful to the end user since they still like to refer to OrderNumber 12345) PK with IDENTITY integer. Since we start to adopt SyncFx and to support offline client creation scenario, we decided to add a new column unique identifier (GUID) as the new PK. 

    in my old working V1.0 implementation, the OrderId will be assigned as negative value (-1, -2 and so on) for the offline created records. when the sync happens, the OrderId will be reassigned with the 'next' number ( the server will perform the max() + 1 on the OrderId column in the table). the logic is implemented as a Stored procedure and it will be called during the insert trigger.

    Update INSERTED

    set OrderId = b.NextNumber

    FROM INSERTED a,

    (

    SELECT SyncGUID, 

    ROW_NUMBER() OVER (ORDER BY SyncGUID) + (SELECT MAX(OrderId) MaxID FROM Order) as NextNumber

    --select * 

    FROm Order 

    where OrderId <= 0

    ) b

    where a.SyncGUID = b.SyncGUID

    However, in V2.0, after the syc, the record is created in the server side  and the OrderId did being updated (from -1 to 'next' number); however, that update change didn’t get download to the client. I was wondering 

    1) i looking into the logic in the _selectchanges() SProc. i seems like i might need to manually manipulate the [local_update_peer_timestamp] in order for the enumeration to pick it up ?! 

     

    2) since the whole logic is running within the insert trigger (which the record is still not committed to the actual table), I am also wondering is it possible to have the enumeration being called again after the record is committed to the server table. (without calling another sync)

     

    3) will the "INSTEAD OF INSERT" trigger offer any helps in this case?! I tried a few time but no workies with my limited knowledge.

     

     

    Any comments would be greatly appreciated?

     

    Thanks,

    --alex


    Tuesday, August 31, 2010 7:07 PM

Answers

  • Sync Fx will not detect the change you did for a specific column in the trigger as change for synchronization. As far as its concerned, it received a new row to be inserted and it inserts it and tags that row as having been inserted. The change in the order id is part of the same insert operation so its not flagged as an update after the insert operation that needs to flagged as a change for the next sync.

    you can try doing a dummy update on the newly inserted rows so that the Sync Fx will update it's metadata that the rows has been updated.

    On the AppliedChanges event on the server side, you can loop thru the dataset in the Context property of the event arguments to find out the newly inserted rows and issue a dummy update.

    Wednesday, September 1, 2010 1:02 AM
    Moderator

All replies

  • Sync Fx will not detect the change you did for a specific column in the trigger as change for synchronization. As far as its concerned, it received a new row to be inserted and it inserts it and tags that row as having been inserted. The change in the order id is part of the same insert operation so its not flagged as an update after the insert operation that needs to flagged as a change for the next sync.

    you can try doing a dummy update on the newly inserted rows so that the Sync Fx will update it's metadata that the rows has been updated.

    On the AppliedChanges event on the server side, you can loop thru the dataset in the Context property of the event arguments to find out the newly inserted rows and issue a dummy update.

    Wednesday, September 1, 2010 1:02 AM
    Moderator
  • Hi JuneT,

    I finally get it working by calling a SProc which performs the max(id)+1 logic by using a simple ADO.NET command.

    One quick comments, it is called after the RelationalSyncProvider.ProcessChangeBatch().

    Originally, I was trying to call that Sproc within the serverProvider ChangesApplied() event. However, because i am using the batching, there is a uncommitted transaction within ChangesApplied() event; that locks the table and hence it also prevent any external update on that table.  

     

    Thanks again!!

    Friday, October 15, 2010 5:13 AM