Auto logging / tracking records changes at MS SQL

Question

how can I set automatic recording all changes that were done at tables records (Updates) and to get some info / logging summery? thanks

Answer 1

There's a few different options available to do this, and also depend on which version of SQL Server you're running. Here they are in order of least complicated to implement and most standard, in my opinion:

1. Temporal Tables - These create a log table for each table you want to track changes on, when and what the change was. It's also possible to extend the table to store who made the change. The log table can be indexed as needed.

2. Change Data Capture - Similar to Temporal Tables, but on a more global level. System tables of the same database are used to log the changes of your tables within that database.

3. Database DML Triggers - You can create an AFTER TRIGGER that manually logs the changes into a separate table. This is more of a homebrew solution and a little more work than using either of the first two features.

4. Change Tracking - Doesn't keep a full history or the timestamp of when the change occurred.

5. Ledger Tables - Only available in latest Microsoft SQL versions, built on top of blockchain technology to improve upon immutability.

6. Audit - This globally tracks multiple events, a subset of which would be DML changes such as UPDATES and INSERTS.

Answer 2

The most straightforward way would be to use SQL Server Auditing but there are alternative options too.

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver15