In this article, we will explore the concept of change tracking in SQL Server and how it can be implemented using triggers. Change tracking is a mechanism that allows us to track the changes made to one or more tables in a database. By keeping a history of these changes, we can easily identify and analyze the modifications made to the data.

There are several ways to achieve change tracking in SQL Server, such as using Change Tracking (CT), Change Data Capture (CDC), Temporal Tables, or triggers. In this article, we will focus on using triggers to implement change tracking.

Preparing the Database

Before we can implement change tracking using triggers, we need to create the necessary database and tables. Let’s start by creating the Users table and inserting some records into it:

CREATE DATABASE ChangeTrackingDemo;
USE ChangeTrackingDemo;

CREATE TABLE Users (
    UserID INT IDENTITY (1, 1),
    FirstName VARCHAR(100),
    LastName VARCHAR(100),
    Age INT
);

INSERT INTO Users (FirstName, LastName, Age)
VALUES ('John', 'Doe', 25),
       ('Olivia', 'Young', 28),
       ('Gavin', 'Carr', 30),
       ('Sean', 'Slater', 35);

Creating the Trigger

Now that we have our Users table set up, let’s create a trigger that will track the changes made to the table. For simplicity, we will start by creating a trigger that only captures insert statements:

CREATE TRIGGER TRG_Users_ChangeTracking
ON Users
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    
    INSERT INTO ChangeTrackingHistory (UserID)
    SELECT i.UserID
    FROM inserted AS i;
END;

In the above trigger, we specify that it should fire after an insert operation on the Users table. When a new record is inserted, the trigger will fetch the UserID of that record and insert it into the ChangeTrackingHistory table.

Verifying the Trigger

Let’s now insert a record into the Users table and verify that the trigger is working correctly:

INSERT INTO Users (FirstName, LastName, Age)
VALUES ('Caty', 'Williams', 26);

SELECT * FROM Users;
SELECT * FROM ChangeTrackingHistory;

As you can see, the trigger successfully captures the UserID of the inserted record in the ChangeTrackingHistory table.

Expanding the Trigger

So far, our trigger only captures insert statements. Let’s modify the trigger to also capture updates and deletes:

ALTER TRIGGER TRG_Users_ChangeTracking
ON Users
AFTER INSERT, UPDATE, 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;

With this modification, the trigger will now capture the UserID of records that are inserted, updated, or deleted from the Users table.

Conclusion

In this article, we have explored the concept of change tracking in SQL Server and how it can be implemented using triggers. Change tracking allows us to keep a history of the changes made to our database, making it easier to analyze and track modifications. By using triggers, we have more control over which data to capture and which to reject.

While the above solution is a basic implementation, there are several ways to improve and optimize it. For example, we can capture the SQL statement used to perform the operation, store the timestamp of trigger execution, or even store the actual data that was changed.

Implementing change tracking in SQL Server can be a valuable tool for auditing and analyzing data modifications. It provides a way to track changes and understand the history of our database.