How to implement audit trail using triggers in SQL

By Database Admin at Feb 12, 2025 0 Comments

How to implement audit trail using triggers in SQL

An audit trail is a method for tracking changes made by users in a database. In industries such as Healthcare, Finance, and Insurance, maintaining a detailed record of all modifications is essential to ensure accountability and prevent unintended alterations to the system.

An effective audit trail should provide a chronological log of changes, including:

Who made the change
What was changed
When the change occurred

Additionally, systems can capture how the user made the change—whether through an application interface or an automated process.

Methods for Implementing an Audit Trail

1. ORM (Entity Framework) Change Tracking
2. SQL Server Audit Feature (Learn More)
3. Database Triggers

In this guide, we’ll demonstrate how to implement an audit trail in MS-SQL using triggers.

Step 1 - Creating a Table to Store Audit Trail Data

CREATE TABLE [dbo].[AuditTrail] 
( 
  [TableName] VARCHAR(100) NULL, 

  [TableId] VARCHAR(100) NULL, 

  [UpdatedBy] INT NULL, 

  [UpdateDate] DATETIME NULL, 

  [UpdateJSON] NVARCHAR(MAX) NULL 

);

This table is designed to store audit trail data for all tables in the database. The current approach stores change information in JSON format, though it can also be stored in XML or key-value columns based on your requirements. You can customize the script to align with your table design.

Column Descriptions:

TableName – Specifies the table where the change occurred.
TableId – Stores the primary key ID of the affected record (assuming a surrogate key).
UpdatedBy – Records the user ID who made the update.
UpdateDate – Captures the timestamp of the change.
UpdateJSON – Stores the previous and new values in JSON format for tracking changes.

The goal of the audit trail process is to record every change made by users in this table, ensuring accountability and traceability.

Step 2 - Creating a Trigger in the Target Table

The following trigger is designed for a sample table called 'Customer'. You should update this script to match your specific table(s).

To understand this code, you need a basic knowledge of SQL Triggers. In summary, a trigger is invoked during bulk updates, requiring the use of standard collections like INSERTED and DELETED to capture the changed data.

The approach here is to generate a JSON string containing the modified fields. This can also be adapted to XML format if needed.

This trigger will insert a new record into the "AuditTrail" table for each modified record, with all changed field values stored in the UpdateJSON column.

ALTER   TRIGGER [dbo].[tr_Customer]
ON [dbo].[Customer] 
AFTER UPDATE 
AS 
BEGIN
	declare @json1 nvarchar(max)
	declare @json2 nvarchar(max)
	declare @updatedBy int
	declare @id int

	SELECT	TOP 1 @id =CustomerId,@updatedBy = ISNULL(UpdatedBy, CreatedBy)
	FROM inserted
	ORDER BY InternalReqId  

	WHILE @id IS NOT NULL
	BEGIN 
		-- get json1 the old value 
		SET @json1 = (select * from deleted WHERE CustomerId= @id 
			FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

	-- get json2 the new value 
	SET @json2 = (select * from inserted WHERE CustomerId= @id 
	FOR JSON PATH, WITHOUT_ARRAY_WRAPPER)

	DECLARE @updateJson nvarchar(max)

	-- get the json for the updated columns
	SET @updateJson = (SELECT j1.[key], j1.[type], j1.[value] as [oldvalue], j2.[value] as [newvalue]
		from openjson(@json1) j1
			left join openjson(@json2) j2 on j1.[Key] = j2.[key]
		where ISNULL(j1.[value], '') <> ISNULL(j2.[value], '')
		FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
		)

	IF (ISNULL(@updateJson, '') <> '')
	BEGIN
		-- store it in the output table 
		INSERT INTO common.AuditTrail(TableName, TableId, UpdatedBy, UpdateDate, UpdateJSON)
		values('Customer', @id, @updatedBy, getdate(), @updateJson)
	END 

	-- get next id 
	SET @id = (SELECT TOP 1 CustomerId
		FROM inserted
		WHERE CustomerId> @id 
		ORDER BY CustomerId)  
	END 
END

Step 3 - Creating a View to Read Audit Trail Data

This view simplifies querying the AuditTrail data by listing each change, including the table name, field name, old value, and new value.

The code assumes the existence of an AppUser table, where the UpdatedBy user ID is stored. If your system uses a different method to retrieve user details, you should modify this accordingly.

Additionally, you may notice the array wrapper ([ & ]) used in the JSON structure. The trigger is designed to exclude this wrapper. If needed, you can adjust both the trigger and this view to match your specific requirements.

create or alter view common.vAuditTrail
as
select t.TableName, t.TableId, t.UpdatedBy, au.FirstName + ' ' + au.LastName UpdatedByName, t.UPdateDate, 
	JSON_VALUE(j.value, '$.key') FieldName,
	JSON_VALUE(j.value, '$.type') FieldType, 
	JSON_VALUE(j.value, '$.oldvalue') OldValue, 
	JSON_VALUE(j.value, '$.newvalue') NewValue
from dbo.AuditTrail t
	LEFT JOIN dbo.AppUser au ON t.UpdatedBy = au.AppUserId
	CROSS APPLY OPENJSON('['+t.UpdateJSON+']') j
WHERE JSON_VALUE(j.value, '$.key')
	NOT IN ('UPdateDate', 'UpdatedBy')

Step 4 - Querying Table-Specific Audit Trail Data

The following is an example query to retrieve Audit Trail data for a specific table, such as 'Customer'.

If the Customer table contains foreign keys (e.g., StatusId referencing a Status table), you must retrieve both the old and new values of Status to display meaningful information in the Audit Report.

Additionally, your target table may contain multiple fields of different data types. You should ensure that each field's values are properly formatted and user-friendly in the final report.

    SELECT t.*,
        CASE t.FieldName
            WHEN 'StatusId' THEN 'Status'
	
        END AS FieldNameDisplay,
        CASE t.FieldName
            WHEN 'StatusId' THEN lv1.ValueTitle
        END AS OldValueName,
        CASE t.FieldName
            WHEN 'StatusId' THEN lv2.ValueTitle
        END AS NewValueName 
    from common.vAuditTrail t 
        left join dbo.Status s1 ON t.FieldName = 'StatusId' AND 
try_convert(int, t.OldValue) = s1.StatusId
        left join dbo.Status s2 ON t.FieldName = 'StatusId' AND try_convert(int, t.NewValue) = s2.StatusId
    WHERE t.TableName = 'Customer'

Key Considerations:

Ensure proper handling of data types for readability.
Modify triggers and views based on business requirements.
Adapt the JSON structure if necessary.