Open In App

How to Maintain Audit Information For DML in SQL?

Improve
Improve
Like Article
Like
Save
Share
Report

Pre-requisites: Audit Trail

In today’s world, data plays a vital role for any organization. Not just for the organization 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 it.

  • An audit trail may be required by your application to maintain what changes are done by who.
  • An audit trail may help you to revert back to any state at any given point in time in case of accidental changes or application errors.
  • An 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 this article, we are going to understand and implement what are the different mechanisms available to implement audit tracking in your application data at the Microsoft SQL Server level. Here, we will see how we can implement an audit mechanism for DML objects i.e. tables.

DML Auditing:

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

Method 1:  Using Triggers

In the first method, we see the traditional approach where most of the organizations are still using or perhaps old legacy applications implemented with this mechanism. In order to keep a track of data changes in your SQL database tables, triggers can be used to keep a 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 to 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)

Output:

Figure 1

In order to track the changes done on your table, we will be implementing the following trigger which will help us to keep a track of previous or new values whenever 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]  NULL,
   [NewRecord]  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
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.

Output:

Figure 2

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. 

Figure 3

That’s it, our trigger is working perfectly and keeping a track of all changes happening on your records in the Employee table. Now, give a try on Delete command and see how it keeps in AuditLog table.

Method 2: System-Versioned Temporal Tables

As per Microsoft, System Versioned temporal tables provided by database which provides built-in features for providing information about your data stored in the table at any given point of time rather than just giving the current data 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, if I have to make it simplified.

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 structured 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.

Figure 4

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.

Working of Temporal Table:

System versioning for a table is implemented as a combination of 2 tables – (1) the current table and (2) the historical table. Within each of those tables, you will find 2 columns with data type datetime2 which are used to define the period of time the record was valid in the table.

  • ValidFrom column: SQL records the start time for the row in this column. 
  • ValidTo column: SQL records the end time for the row in this 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')

Output:

Figure 5

Now let’s try to update some records to see how the audit is done using System Versioned Temporal Tables. Execute 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.

Output:

Figure 6

Now we can easily identify what values have been changed for your 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.



Last Updated : 24 Aug, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads