I have a database consisting of several tables and in a separate table, I would like to store every column within every table and the last time the data in the columns was modified (i.e. following an UPDATE, INSERT or DELETE) statement.

If I only update one column in a table, the record for that column in my log would then update with the last modified timestamp.

Is this possible and is there a suggested approach for it? My initial thought was potentially to use a database-level trigger but wasn't sure if this could be setup to monitor changes to all columns in all tables. I don't want to have to create an individual trigger on each table but wasn't sure how else I could monitor changes at a column level.

Something I am exploring: https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver15

  • You need to define exactly what information you need to log. For instance, if you need who did the modification, then a bunch of options are off the table. – Tibor Karaszi Aug 19 at 14:46
  • @TiborKaraszi The key details to log would just be the column which has been modified and just a timestamp. And optionally, the system user who implemented the change. – Alex Aug 19 at 14:49
  • By "system user", you mean the SQL server login name. That disqualifies stuff like Change Tracking, Change data Capture and temporal tables. You now have creating your own Extended Events trace, Server Audit (which builds on XE) and triggers left. And, now a single trigger can not be fired for modifications on several tables. The closest you can come is to create the trigger in CLR and create that CLR trigger on each and every table. You now have only one code base (which has to handle modifications on all tables). – Tibor Karaszi Aug 19 at 15:39
  • 1
    You don't mind the performance impact this will have if you manage to implement this? Anyway, doesn't Microsoft provide this kind of auditing? – Gerard H. Pille Aug 19 at 18:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.