0

I want to update LastUpdatedOn datetime2 column every time a row is updated in a SQL Server table. To accomplish this, I was thinking of using an After Update trigger to set LastUpdatedOn to sysutcdatetime(). This trigger is also responsible for detecting column changes and saving the old values to a history table. My question is, will the trigger approach save an accurate representation of the time a row is updated? Is there a time delay between an actual update vs. After Update trigger updating LastUpdatedOn?

Also, reading http://technet.microsoft.com/en-us/library/bb630387.aspx, it looks like GetSystemTimeAsFileTime() may not be accurate across multiple servers. In a sharded database architecture with a lot of concurrent writes, what's the most reliable way to detect event times so that I know the order of events across the whole cluster is correct?

P.S. I've thought about using Change Data Capture, but it's not available in SQL Azure.

Mark13426
  • 2,569
  • 6
  • 41
  • 75

1 Answers1

0

There could be a delay, depending on how many rows you're updating in a single statement and how log as it takes, because the trigger is fired at the very end. I would recommend updating the time inside the actual update statement.

Moreover, use output clause to move data to history table.

dean
  • 9,960
  • 2
  • 25
  • 26