Aveek Das
Capturing updates and Operation - change tracking in sql server

Understanding Change Tracking in SQL Server using Triggers

December 9, 2020 by

In this article, I am going to explain what change tracking is in SQL Server and why do we need it. I will also illustrate the same using some practical examples using triggers in SQL Server. Change tracking as the name suggests, is a mechanism that helps us to identify the changes in the database as the application grows. In other words, it enables us to have a history of the changes that have been made to one or more tables in the database. The changes can be considered as either INSERTs, UPDATEs, or DELETEs.

Overview

Change tracking in SQL Server can be achieved using a few of the inbuild technologies such as Change Tracking (CT), Change Data Capture (CDC), Temporal Tables, or by using simple triggers in SQL Server. In this article, I will consider the method of using triggers to implement the change tracking in SQL Server. As you might be aware, a trigger in a database is a simple program that is executed when an event occurs. We are going to leverage this concept and execute a trigger whenever a record in a table is either inserted, updated, or deleted. If you have a very big database, you can control which columns or tables to install the triggers on. This gives greater control over the other inbuilt techniques for implementing change tracking in SQL Server.

Preparing the database

Now that we have some idea about how the triggers will work, let us first create the database and tables on which the triggers can be installed. You can execute the script below to create the Users table and insert a few records into the table.

Users table created for change tracking in sql server

Figure 1 – Users table created

Now that our Users table has been created, let us create the ChangeTrackingHistory table. In this ChangeTrackingHistory table, we are going to store the UserID of each of the records from the Users table that has been inserted, updated or deleted. By doing this, we can keep a track of which record has been operated on. We can use this information to lookup at the Users table to get an understanding of the operation performed.

Creating the trigger

To keep things simple, let us begin by creating the trigger on the insert statement only. This means our trigger will work for insert statements only and not for updates or deletes. You can create the trigger by executing the script below. Please remember that creating the trigger is a DDL statement and you must have sufficient permissions on the table on which the trigger is meant to be created.

As you can see in the script above, we have specified the trigger to fire on insert on the table Users. As soon as there is a new record in the Users table, the trigger will fetch the UserID of that record and insert it into the ChangeTrackingHistory table. You can also verify that the trigger has been installed on the correct table by expanding the Triggers under the selected table on the Object Explorer.

Verify Trigger from the Object Explorer

Figure 2 – Verify Trigger from the Object Explorer

Inserting records in the Users table

Let us now see this in action. I am going to insert a record in the Users table and verify the data in the ChangeTrackingHistory table.

Trigger executed on Insert - change tracking in sql server

Figure 3 – Trigger executed on Insert

As you can see in the figure above, when the record is inserted into the Users table, we have also inserted the new UserID in the ChangeTrackingHistory table. In this way, we can now say that a new record with UserID5” has been inserted into the Users table.

Update trigger to capture Deletes

So far, we have seen that our trigger handles the insert statements well. Let us now modify our trigger such that we can also capture the records that are deleted from the Users table. In order to do that, we are going to modify the select statement inside the trigger to fetch data from the deleted table. You can use the following script to modify the trigger to capture deletes to the table.

Deleting records from the table

Let us now delete a record from the table and view the changes in the ChangeTrackingHistory table.

Deleted records from the Users Table - change tracking in sql server

Figure 4 – Deleted records from the Users Table

As you can see that the UserID of the deleted record has been captured in the ChangeTrackingHistory table.

However, if you take a look at the ChangeTrackingHistory table now, you can see that we have two records that are inserted and deleted, but there’s no way to identify what was the operation performed in each of those records. Without this information, the change tracking table makes no sense because after few operations it would be almost impossible to identify the operations performed on the records and the purpose to track the changes will not be fulfilled. To overcome this, we can add a new column Operation in the ChangeTrackingHistory table that will store the nature of the operation performed on the table while capturing the UserID. It will help determine whether the record was inserted, updated or deleted.

Update trigger to capture Updates and Operation

You can use the following script to update the table to add the new column and the trigger to capture updates, along with inserts and deletes. The logic behind capturing updates is that SQL Server first deletes the old record and then inserts a new record with the updated value. Thus, in case of an update, both the inserted and deleted tables will hold the same UserID.

Let us now update a record in the Users table and verify the changes.

Capturing updates and Operation - change tracking in sql server

Figure 5 – Capturing updates and Operation

We can also capture the operations for the inserts and updates from now on.

INSERT INTO Users(FirstName, LastName, Age) VALUES (‘Gregg’, ‘Pitt’, 21)

Capturing Insert and Delete operations - change tracking in sql server

Figure 6 – Capturing Insert and Delete operations

Now, this solution can be used to track changes to an existing table when there are any operations performed in it. You can also modify the script to capture only the operations that are of interest to you. Although this is a very basic script, there is always some scope for improvement in this solution.

Scope of improvement

As mentioned earlier, there is a provision for improving and optimizing the above trigger, but these are beyond the scope of this article. Just to name a few, you can consider the following improvements that can be implemented to make this solution more robust.

  1. Capture SQL Statement – You can add a column that will store the SQL statement that was used to perform the operation
  2. Capture trigger execution timestamp – Add a column that will store the timestamp when the trigger has been executed
  3. The Store changed data – Right now, we only store the UserID of the record that has been deleted or updated. The solution can also be improved by storing the actual data that has been deleted or updated

Conclusion

In this article, we have understood what is change tracking in SQL Server and the underlying concept behind using such a mechanism. We have also seen various change tracking solutions as offered by Microsoft out of the box directly. Although you can use the above-mentioned solutions, I prefer using triggers for some of my data applications as it allows me to have more control over which data to capture and which one to reject. I would suggest reading another great article: Creating a SQL Server audit using SQL Server Change Tracking that explains how we can use change tracking in SQL Server to create an audit trail.

Aveek Das
General database design

About Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure. He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums. In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn View all posts by Aveek Das

628 Views