How to audit the data change in SQL

Question

We are currently running SQL 2017. We are trying to track the data change with information like DateCreated,CreatedBy for new record added and DataChanged and ChangedBy for data updated or deleted. What is the best way to track these information? Thanks

Answer 1

An option is DML Trigger, see

https://learn.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers?view=sql-server-ver16

Answer 2

Hi，szh8866-8371

SQL Server Audit (Database Engine)(recommend)

Audit can monitor two levels in SQL Server: the server level and the database level. Each level has corresponding Action Groups and Actions. Carefully read the official documentation. Below is a screenshot of my example：

Create a trigger and a log table to record the operations.

Here is an example:

CREATE TRIGGER trgAfterUpdate ON [dbo].[YourTable] AFTER UPDATE 
AS BEGIN 
AuditTable INSERT INTO [dbo].[AuditTable] (ModifiedBy, ModifiedDate, Action, Details) 
SELECT 
SYSTEM_USER, 
GETDATE(), 
'UPDATE', 
'An update has been performed on ID ' + CAST(i.ID AS VARCHAR)
FROM inserted i; END;

• Temporal tables（As Erland recommended）
• If you just want to audit the DML changes (insert, update, and delete operations) that were made to user tables in a database.

Try this method:

Change data capture and Change tracking.

Best regards,

Mikey Qiao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it.

Answer 3

That depends a whole lot on your requirements. If you want dates for every change, the above is obviously insufficient.

If you want audit of every change, you could consider using temporal tables, but you can also expect your disk usage to grow a lot in that case.