In this article, you will understand and implement the different mechanisms available to implement audit tracking in your application data at Microsoft SQL Server level.
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.
- Audit trail may be required by your application to maintain what changes are done by who.
- 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.
- 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'))
SET @NewRecord = (SELECT * FROM Inserted FOR XML PATH('Employee'), _
TYPE, ROOT('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:
- While creating a table, you must specify clause
SYSTEM_VERSIONING = ON
. - 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
. - Specifying a
History
table name is optional, but it is recommended to provide. - 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
- 8th August, 2022: Initial post