How to audit the data change in SQL

szh8866-8371 120 Reputation points
2024-04-25T15:46:33.6133333+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,917 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.4K Reputation points
    2024-04-25T21:15:53.4166667+00:00

    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.

    0 comments No comments

  2. MikeyQiao-MSFT 655 Reputation points Microsoft Vendor
    2024-04-26T06:03:15.6566667+00:00

    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:

    User's imageUser's image

    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.

    0 comments No comments

  3. Olaf Helper 41,571 Reputation points
    2024-04-26T06:03:52.9233333+00:00
    0 comments No comments