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