Different Ways to Maintain Audit Information for DML in SQL

Introduction

In today's world, data plays a vital role for any organisation. Not just for organisations, but also for customers. It becomes vital in any application to maintain track of changes happening in your data to keep audit trails.

There are numerous reasons to implement an auditing mechanism for your application or data. Let's see some of them.

1. Audit trail may be required by your application to maintain what changes are done by who.
2. Audit trail may help you to revert back to any state at any given point of time in case of accidental changes or application errors.
3. Audit trail may help you to figure out patterns involved or carried out by users which will help to enhance your application or future development opportunities.

In today's topic, we are going to understand and implement what are the different mechanisms available to implement audit tracking in your application data at Microsoft SQL Server level.

We are going to talk about how we can implement an audit mechanism for your DML objects.

DML Auditing (Table Audit)

Let’s dive into different solutions available to implement audit tracking for your data in SQL server. The solutions mentioned below are implemented in Microsoft SQL Server 2016 database.

Solution #1 (Triggers)

In solution 1, we are going to talk about the traditional approach which most organisations are still using or perhaps old legacy applications implemented with this mechanism.

In order to keep track of data changes in your SQL database tables, triggers can be used to keep track of previous and new changes.

Let’s first create the following table in our SQL Server using SSMS.

CREATE TABLE [dbo].[Employee]
(
    [Employee_Id] [int] NOT NULL,
    [Employee_Name] [nvarchar](100) NOT NULL,
    [Employee_Designation] [nvarchar](50) NOT NULL,
    [Employee_Salary] [float] NOT NULL
)

Now, let’s add some records into the Employee table.

INSERT [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], _
[Employee_Salary]) VALUES (1, 'David Schofield', 'Technical Manager', 4000000)

INSERT [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], _
[Employee_Salary]) VALUES (2, 'John Smith', 'Director', 10000000)

INSERT [dbo].[Employee] ([Employee_Id], [Employee_Name], [Employee_Designation], _
[Employee_Salary]) VALUES (3, 'Anna Boston', 'Engineer', 200000)

Your table will look like this:

In order to track the changes done on your table, we will be implementing the following trigger which will help us to keep track of previous or new values wherever Insert, Delete operations are performed on the Employee table.

In order to keep audit records or historical records, we are going to create a new table called “AuditLog” which will be populated by triggers whenever any update or delete operations happen.

CREATE TABLE [dbo].[AuditLog]
(
	[AuditLog_Id] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
	[Action] [nvarchar](50) NOT NULL,
	[PreviousRecord] [xml] NULL,
	[NewRecord] [xml] NULL,
	[ModifiedOn] [datetime] NOT NULL
)

Now, let’s write a trigger on your table Employee.

ALTER TRIGGER [dbo].[trg_AuditTable] 
   ON  [dbo].[Employee] 
   AFTER DELETE, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;
	DECLARE @PreviousRecord AS XML
	DECLARE @NewRecord AS XML
	
	DECLARE @Action VARCHAR(10)
	IF EXISTS(SELECT * FROM deleted)
		SELECT @Action = 'DELETE'
	IF EXISTS(SELECT * FROM inserted)
	  IF EXISTS(SELECT * FROM deleted)
		SELECT @Action = 'UPDATE'

	SET @PreviousRecord = (SELECT * FROM Deleted FOR XML PATH('Employee'), _
	TYPE, ROOT('Record')) -- READ THE PREVIOUS / CURRENT STATE OF THE RECORD
	SET @NewRecord = (SELECT * FROM Inserted FOR XML PATH('Employee'), _
	TYPE, ROOT('Record')) -- -- READ THE NEW STATE OF THE RECORD

	INSERT INTO [dbo].[AuditLog]
    (
		 [Action]
		,[PreviousRecord]
		,[NewRecord]
		,[ModifiedOn]
	)
     VALUES
    (
		 @Action
		,@PreviousRecord
		,@NewRecord
		,GETDATE()
	)
END

And that’s all! Your audit mechanism is in place for your Employee table. Let’s try performing some UPDATE and DELETE operations on your Employee table and see how it logs into the AuditLog table.

UPDATE [dbo].[Employee] 
SET 
     [Employee_Designation] = 'Engineering Manager'
    ,[Employee_Salary] = '50000'
WHERE [Employee_Id] = 3 

Execute above UPDATE command, and check AuditLog table to find out audit or history being maintained.

Now, we can see that the trigger is working perfectly. It records the operation as UPDATE and inserts Previous and New Record values in the table as XML. Let’s try to open those XMLs to understand what has been changed.

That’s it, our trigger is working perfectly and keeping a track of all changes happening on our records in the Employee table.

Solution #2 (System-Versioned Temporal Tables)

What Microsoft says - Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time.

I would say System Versioned temporal tables are the modern version of the legacy solution of implementing via Triggers.

Let’s see how. In our Solution #1, we saw that every record updated or deleted will track every column change and add it into the AuditLog table in XML format. There is no specific logic written to identify what column changed but simply making an entry into a table with previous and new records with the help of special magic tables.

With System Versioned Temporal tables, it happens exactly the same way but with more structures and simple manners. Microsoft SQL Server takes care of maintaining the history automatically. Let’s understand more by implementing it.

This time, we will create a new table called STUDENT.

CREATE TABLE [dbo].[Student]
(
	[Student_Id] [int] NOT NULL PRIMARY KEY IDENTITY (1, 1),
	[Student_Name] [nvarchar](100) NOT NULL,
	[Student_Address] [nvarchar](50) NOT NULL,
	[Student_City] [nvarchar](50) NOT NULL,
	[ValidFrom] datetime2 GENERATED ALWAYS AS ROW START,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StudentAuditLog)) 

Once you create the table in the database, check Tables in the Object Explorer in your SSMS.

You will find that the table name is now showing as System versioned. If you expand further, you will find an additional table called “StudentAuditLog'' which is going to be your historical table which will hold all the changes done on the Student table.

Let’s understand some of the thumb rules for applying system versioned temporal tables:

1. While creating a table, you must specify clause SYSTEM_VERSIONING = ON.
2. A system-versioned temporal table must have a primary key defined and have exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared as GENERATED ALWAYS AS ROW START / END.
3. Specifying a History table name is optional, but it is recommended to provide.
4. This feature is only available from SQL Server 2016 (13.x) and later.

How Does the Temporal Table Work?

System-versioning for a table is implemented as a pair of tables, a current table and a history table. Within each of these tables, two additional datetime2 columns are used to define the period of validity for each row:

• Period start column: The system records the start time for the row in this column, typically denoted as the ValidFrom column.
• Period end column: The system records the end time for the row in this column, typically denoted as the ValidTo column.

The current table contains the current value for each row. The history table contains each previous value (the old version) for each row, if any, and the start time and end time for the period for which it was valid.

Now, let’s add some records into the Student table.

INSERT [dbo].[Student] ([Student_Name], [Student_Address], _
[Student_City]) VALUES ('John Smith', '45 Street Avenue', 'New York')
INSERT [dbo].[Student] ([Student_Name], [Student_Address], _
[Student_City]) VALUES ('Anna Boston', '511 Avenue', 'New York') 

Now let’s try to update some records to see how the audit is done using System Versioned Temporal Tables. Execute the following queries to update the values in the Student table.

UPDATE [dbo].[Student]
SET
    [Student_Address] = 'Madison Road'
   ,[Student_City] = 'Washington DC'
WHERE [Student_Id] = 1

Now, if we query Select statements on both current table and historical table, i.e., Student and StudentAuditLog respective tables, we will get the result like below:

Awesome, right! Now we can easily identify what values have been changed for records. The good part here compared to the trigger solution is - you will get a much structured way of auditing. It is exactly the way your table schema is, easy to write the queries to pull out the historical data and represent.

History

• 8^th August, 2022: Initial post