How to audit records in Sql Server ?

Introduction

Do you know how to audit records in sql server ?
Sql server 2016 had introduced temporal tables that helps in maintaining the history of the records in a table.

What is temporal table ?

The traditional way of maintaining an audit history of the table was to create a separate table and write triggers that would insert records in the audit table whenever the record in the parent table is inserted,updated or deleted. The new table that was created to maintain the audit data had the same structure as that of the main table with an Id column. It was completely dependent on developers to design the schema of the audit table they need depending on their use case.

But thanks to Sql Server for providing us an built-in feature of creating table that would take care of the auditing on its own. This database feature is available with sql server 2016 and above. This feature is also called Temporal Tables or System Versioned Table.

Why do we need audit history of a table ?

Working on several projects I have mostly found the historical data to be very useful in troubleshooting issues by looking into the state of the record in a specific point in time. Here are some of the scenarios in which audit history would be useful -

1. Troubleshooting issues.
2. Recovering the data in case data gets corrupted due to defects in the application.
3. Analysing the data to figure out the trend in the business.
4. Showing up the history of the changes of a record in the application.

Structure of Temporal Tables

In this feature we have two tables that are related to each other just like the traditional audit table.

The first table keeps the current state of the record and is called current table or temporal table.

The second table which is present within the first table in sql server keeps track of the audit history of the records and is called the history table.

In the temporal table when a record is inserted there is no action done on the history table.

When a record in the current table is updated the previous state of the record gets inserted in the history table.

Similarly when a record is deleted from the current table another record gets inserted in the history table.

The history table keeps track of the validatity of the records with two datetime2 columns which is ValidFrom and ValidTo date.

We also have similar columns in the main table. Although I am not sure of the use of these two columns in the main table because we usually maintain CreatedUser,CreatedDate,UpdatedUser,UpdatedDate columns in our transaction tables.

The ValidFrom column in the main table contains the date in which the record was inserted and the valid to date contains the max value of datetime2 to indicate that the current record is valid.

Creating a temporal table

The below script creates a temporal table named User when executed in Sql server.

CREATE TABLE dbo.[User]
(
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Email] varchar(100) NOT NULL
  , [Phone] varchar(10)
  , [Department] varchar(100) NOT NULL
  , [Address] nvarchar(1024) 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.UserHistory));

The table looks like the below screenshot on sql server.

Inserting records in temporal table

We will now insert a record in the temporal table.

INSERT INTO [dbo].[User]
           ([UserID]
           ,[Name]
           ,[Email]
           ,[Phone]
           ,[Department]
           ,[Address])
     VALUES
           (1
           ,'John Doe'
           ,'johndoe@mycomp.com'
           ,1111222233
           ,'Sales'
           ,'AUDITING	1 CITY HALL SQ, M 04	BOSTON MA')
GO

Notice the value in the ValidTo column.

Updating record in temporal table

Let us now make two updates first updating the emailid and then updating the phone column.

UPDATE [dbo].[User]
   SET [Email] = 'johndoe@gmail.com'
 WHERE UserID = 1
GO
UPDATE [dbo].[User]
   SET [Phone] = '1234567890'
 WHERE UserID = 1
GO

Deleting record in the temporal table

Now let us delete the record from the User table.

DELETE FROM [dbo].[User]
      WHERE UserID = 1
GO

To sum up we did the following operations on the table -

1. Inserted a new record in the user table
2. Updated the email id of the record
3. Updated the phone of the record
4. Deleted the record

Now lets check the records in the User and the UserHistory tables -

SELECT * FROM [dbo].[User]
SELECT * FROM [dbo].[UserHistory]

Conclusion

In this post we have seen how sql server makes it very easy for us to do the auditing of the table by taking up the activity of creating and managing the history table.

We understood the following points -

1. What is Temporal Table ?
2. Why do we need audit history of a table ?
3. Structure of Temporal Tables
4. Creating a temporal table
5. Inserting records in temporal table
6. Updating record in temporal table
7. Deleting record in the temporal table
8. The effect of the insert,update and delete operations on the Temporal and the History table.

Please share the post by clicking the social media icons at the beginning of the post.
Thank you for reading and see you in the next post !👋