locked
Using an existing timestamp - UpdataTrigger is not created? RRS feed

  • Question

  •  

    Hi there!

     

    Using ADO Sync. Framework, everything works when creating an empty database with a table and data etc.

     

    But, the problem is that we have existing data, with tables containing timestamps. When setting up the using the Visual Studio Sync tool(LcalDataCache) it does not work - I noted that it does not create UpdateTriggers..

     

    Should I just manually create a trigger in the table - the table already containing a timestamp column?

     

    Thanks

    Chris

     

    • Moved by Hengzhe Li Friday, April 22, 2011 7:53 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, August 8, 2008 10:17 AM

Answers

  • Hi, Chris,

     

    Designer doesn't generate trigger for existing timestamps, because designer does not want to mess user's existing data.

    You need to manually create the trigger for insert, update, and delete.

     

    If generating trigger for existing timestamps (if no trigger available) is a need, we might put that into our feature request list.

     

    Thanks!

    Yang

    Wednesday, August 13, 2008 2:28 AM

All replies

  • Hello,

     

    We can only have one table column defined as the timestamp column.  So if you have one already, you can re-use it for record update changes.  The timestamp column is auto-updated when the row is created/updated.  However there is a problem with it - you may see under-enumeration problem because the timestamp value is created when a transaction is started, not ended.

     

    The alternative (solution) is to use UTC datetime for the tracking column.  You can create more than 1 datetime columns in a table.

     

    If you can not change the table schema, then you can use SQL Server Change Tracking feature to track changes if you use SQL Server 2008.

     

    Thanks.

    Saturday, August 9, 2008 4:45 PM
    Answerer
  • Hi, Chris,

     

    Designer doesn't generate trigger for existing timestamps, because designer does not want to mess user's existing data.

    You need to manually create the trigger for insert, update, and delete.

     

    If generating trigger for existing timestamps (if no trigger available) is a need, we might put that into our feature request list.

     

    Thanks!

    Yang

    Wednesday, August 13, 2008 2:28 AM
  • But when I look at the code, it seems that an UpdateTrigger for tables with timestamp (or what should now be called rowversion) on them are not needed (as all you're doing in the trigger is updating the values which are already being auto-incremented by the engine.  While in a tx this might be an issue, I doubt it is for most people.  The problem I am coming into is when I try to re-use the timestamp, the synchronization finds no data whatsoever.  I haven't yet figured out why the replica is not being created correclty though on manual syncing, no data is transferred either.

     

    Any ideas to nudge me in the right direction?

    Monday, November 17, 2008 10:16 PM
  • Ok, the issue seems to be around the SelectnewAnchorCommand generated by the code in the Agent. The problem is that it assumes that the initial load of the replica worked (which would only get recent changes). I think this is a bug in the tooling, not in the generated code.  If the tool had not used this same method to load the original table with existing rowversions, then the SelectNewAnchorCommand would be fine.  In this case I have to work around it and load the .sdf manually the first time.

     

    Ideas?

    Monday, November 17, 2008 10:32 PM