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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
-- Create the database CREATE DATABASE ChangeTrackingDemo GO USE ChangeTrackingDemo GO CREATE TABLE Users( UserID INT IDENTITY(1,1), FirstName VARCHAR(100), LastName VARCHAR(100), Age INT ) GO INSERT INTO Users(FirstName, LastName, Age) VALUES ('John', 'Doe', 25) INSERT INTO Users(FirstName, LastName, Age) VALUES ('Olivia', 'Young', 28) INSERT INTO Users(FirstName, LastName, Age) VALUES ('Gavin', 'Carr', 30) INSERT INTO Users(FirstName, LastName, Age) VALUES ('Sean', 'Slater', 35) GO SELECT * FROM Users GO |
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.
|
1 2 3 4 5 |
CREATE TABLE ChangeTrackingHistory( ChangeTrackingID INT IDENTITY(1,1), UserID INT ) GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Create the trigger for inserts CREATE TRIGGER dbo.TRG_Users_ChangeTracking ON dbo.Users AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO ChangeTrackingHistory(UserID) SELECT i.UserID FROM inserted AS i; END GO |
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.
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.
|
1 2 3 4 5 6 7 8 |
INSERT INTO Users(FirstName, LastName, Age) VALUES ('Caty', 'Williams', 26) GO SELECT * FROM Users GO SELECT * FROM ChangeTrackingHistory GO |
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 UserID “5” 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Update the trigger for inserts and deletes ALTER TRIGGER dbo.TRG_Users_ChangeTracking ON dbo.Users AFTER INSERT, DELETE AS BEGIN SET NOCOUNT ON; INSERT INTO ChangeTrackingHistory(UserID) SELECT i.UserID FROM inserted AS i UNION SELECT d.UserID FROM deleted AS d; END GO |
Deleting records from the table
Let us now delete a record from the table and view the changes in the ChangeTrackingHistory table.
|
1 2 3 4 5 6 7 8 |
DELETE FROM Users WHERE UserID = 3 GO SELECT * FROM Users GO SELECT * FROM ChangeTrackingHistory GO |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
-- Alter the table to capture updates and add operation ALTER TABLE ChangeTrackingHistory ADD Operation VARCHAR(10); GO -- Install the trigger to capture updates ALTER TRIGGER dbo.TRG_Users_ChangeTracking ON dbo.Users AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON; WITH cte1 AS( SELECT d.UserID AS DeletedID ,i.UserID AS InsertedID FROM Deleted d FULL OUTER HASH JOIN Inserted i ON i.UserID = d.UserID ), cte2 AS( SELECT COALESCE(InsertedID,DeletedID) AS UserID ,CASE WHEN InsertedID IS NOT NULL AND DeletedID IS NOT NULL THEN 'Update' WHEN InsertedID IS NOT NULL AND DeletedID IS NULL THEN 'Insert' WHEN InsertedID IS NULL AND DeletedID IS NOT NULL THEN 'Delete' ELSE '' END AS Operation FROM cte1 ) INSERT INTO ChangeTrackingHistory(UserID,Operation) SELECT UserID,Operation FROM cte2; END GO |
Let us now update a record in the Users table and verify the changes.
|
1 2 3 4 5 6 7 8 9 10 11 |
UPDATE Users SET FirstName = 'Brian' WHERE UserID = 2 GO SELECT * FROM Users GO SELECT * FROM ChangeTrackingHistory GO |
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)
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO Users(FirstName, LastName, Age) VALUES ('Gregg', 'Pitt', 21) GO DELETE FROM Users WHERE UserID = 4 GO SELECT * FROM Users GO SELECT * FROM ChangeTrackingHistory GO |
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.
- Capture SQL Statement – You can add a column that will store the SQL statement that was used to perform the operation
- Capture trigger execution timestamp – Add a column that will store the timestamp when the trigger has been executed
- 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.
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
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021