Include actual commit timestamp in row column

Transact-SQL
1

Hello,
I have a use case for SQL server where we want to facilitate a protocol to notify clients of changes in our datamodel. We currently have this implemented by registering all changes to our entities in a log table for each entitiy.

When we insert/update/delete an entity we add a log record using triggers. I know...............
This logrecord has a modificationtimestamp in it which is set in the trigger.

Now I have a problem when concurrent transactions are changing the database.
Within longer running transactions I notice timestamps set on individual rows within the transaction are not the same.

The prootocol notifies clients with current timestamps to allow them to become in sync by querying the server. After the server the client conludes it is in sync until the next timestamp.

If a larger/slower transaction is executed in parallel to a smaller/faster the possibility occurs that changes in the beginning of the larger transaction are missed.

So what I basically need is a way to set the timestamp of ALL records within the transaction to the actual timestamp when the transaction is finsihed.

I tried with no success:

  • after update triggers on the log tables
  • rowversion
  • timestamp

Any ideas?

2

The only way you can pass a variable/info to a trigger is via CONTEXT_INFO. So conceivably you could set a variable to the current timestamp at the beginning of your stored proc, store the value of that in CONTEXT_INFO and use that in the trigger code to update the timestamp column in your audit tables.

You have to be careful about using CONTEXT_INFO though. For one thing, its scope is the session. Also, you have to cast the timestamp to varbinary because CONTEXT_INFO requires varbinary.

That said, there seems to be something unseemly about a client looking for timestamps in various tables to determine whether it has stale data or not. If you have the flexibility to redesign, the system, perhaps a single table that holds that information would be a better design.

3

Hi James,

thanks for your prompt answer.

I think even setting the current timestamp in a variable at the beginning of the transaction would not help.
As I need the actual timestamp that committed data becomes available for querying clients.

I'm going to think of a redesign of this architecture.

Thanks for your thoughts.