(untagged)

Change logging, tracking and reverting using SQL Server

Aqeeel

Rate me:

0.00/5 (No votes)

20 Dec 2017CPOL3 min read

5.8K

Change logging, tracking and reverting using SQL Server

Introduction

One of the easiest way to maintain change log in database is to create a Change Logging table. We can create this table for every table on which we need to track changes. This is an efficient way to track multiple things:

who changed the data,
which data was updated and
when the change occurred

Background

Recently we faced an issue in which data was updated by a user which resulted in some serious configuration problems in application. There was no mechanism in application to back track who did the changes and what changes were made in the database.

A solution was needed to address the issue, that all the changes should be tracked against some specific tables which contain critical data. Solution should also have an option to revert the data back to any particular instance as well.

Here, in this article, we will demonstrate a similar change logging, tracking and reverting procedures and techniques using SQL Server.

Implementation

Suppose we have a table tb_Supplier. This table contains the information regarding the supplier who has been providing different commodities. In this article, we will play with only one table.

Table Supplier, has some sensitive information like Tax Number, License Number, Pre-Qualification Number and its Expiry Date. Updating this information must be logged in database. In case any information requires revert, it should also be done easily.

Below is the SQL Script to create concerned table, tb_Supplier

SQL

CREATE TABLE [dbo].[tb_SupplierLog](

       [SupplierLogID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

       [Operation] [nvarchar](10) NULL,

       [SupplierID] [int] NULL,

       [SupplierName] [nvarchar](250) NULL,

       [InBusinessSince] [datetime2](7) NULL,

       [PreQualificationNumber] [nvarchar](50) NULL,

       [PreQualificationDate] [datetime] NULL,

       [PreQualificationExpiryDate] [datetime] NULL,

       [IsActive] [bit] NULL,

       [CreatedBy] [int] NULL,

       [CreatedOn] [datetime] NULL,

       [UpdatedBy] [int] NULL,

       [UpdatedOn] [datetime] NULL

)

To keep a track of changes made we create another table, tb_SupplierLog,

SQL

CREATE TABLE [dbo].[tb_SupplierLog](

       [SupplierLogID] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,

       [Operation] [nvarchar](10) NULL,

       [SupplierID] [int] NULL,

       [SupplierName] [nvarchar](250) NULL,

       [InBusinessSince] [datetime2](7) NULL,

       [PreQualificationNumber] [nvarchar](50) NULL,

       [PreQualificationDate] [datetime] NULL,

       [PreQualificationExpiryDate] [datetime] NULL,

       [IsActive] [bit] NULL,

       [CreatedBy] [int] NULL,

       [CreatedOn] [datetime] NULL,

       [UpdatedBy] [int] NULL,

       [UpdatedOn] [datetime] NULL

)

Whenever any operation is performed on table tb_Supplier it will be logged in its corresponding log table, i.e. tb_SupplierLog. Please note that we have two additional columns in logging table. First is SupplierLogID which is primary key for logging table and Operation that will identify the operation that was performed.

We will write Stored Procedures to make sure the logic is written once and is reused. First we implement the SelectByID, Insert, Update and Delete stored procedures. Their SQL Scripts are as follow:

SQL

CREATE PROCEDURE [dbo].[sp_Supplier_SelectByID]
    -- Add the parameters for the stored procedure here
    @SupplierID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT    *
    FROM    [dbo].[tb_Supplier] WITH (NOLOCK)
    WHERE    SupplierID = CASE WHEN @SupplierID = -1 THEN SupplierID ELSE @SupplierID END;
    
END

GO

CREATE PROCEDURE [dbo].[sp_Supplier_Insert]
    -- Add the parameters for the stored procedure here
    @SupplierID INT OUTPUT,
    @SupplierName NVARCHAR(250),
    @InBusinessSince DATETIME2,
    @TaxNumber NVARCHAR(50),
    @LicenseNumber NVARCHAR(50),
    @IsActive BIT,
    @PreQualificationNumber NVARCHAR(50),
    @PreQualificationDate DATETIME,
    @PreQualificationExpiryDate DATETIME,
    @UserID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF (    EXISTS(    SELECT    [SupplierID]
                    FROM    [dbo].[tb_Supplier]
                    WHERE    [SupplierName] = @SupplierName
                    )
            )
    BEGIN
        RAISERROR('Supplier already exists', 16, 1);
    END
    ELSE
    BEGIN
        INSERT INTO
            [dbo].[tb_Supplier]
                (    SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                    IsActive,
                    PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                    CreatedBy, CreatedOn)
            VALUES
                (    @SupplierName, @InBusinessSince, @TaxNumber, @LicenseNumber,
                    @IsActive,
                    @PreQualificationNumber, @PreQualificationDate, @PreQualificationExpiryDate,
                    @UserID, GETDATE());

        SET @SupplierID = SCOPE_IDENTITY();

        INSERT INTO
            [dbo].[tb_SupplierLog]
                (    Operation, SupplierID,
                    SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                    IsActive,
                    PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                    CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
        SELECT    'INSERT', @SupplierID,
                SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                IsActive,
                PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
        FROM    [dbo].[tb_Supplier] WITH (NOLOCK)
        WHERE    SupplierID = @SupplierID;
    END

END

GO

CREATE PROCEDURE [dbo].[sp_Supplier_Update]
    -- Add the parameters for the stored procedure here
    @SupplierID INT OUTPUT,
    @SupplierName NVARCHAR(250),
    @InBusinessSince DATETIME2,
    @TaxNumber NVARCHAR(50),
    @LicenseNumber NVARCHAR(50),
    @IsActive BIT,
    @PreQualificationNumber NVARCHAR(50),
    @PreQualificationDate DATETIME,
    @PreQualificationExpiryDate DATETIME,
    @UserID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF (    EXISTS(    SELECT    [SupplierID]
                    FROM    [dbo].[tb_Supplier]
                    WHERE    [SupplierID] <> @SupplierID
                    AND        [SupplierName] = @SupplierName
                    )
            )
    BEGIN
        RAISERROR('Supplier already exists', 16, 1);
    END
    ELSE
    BEGIN
        UPDATE    [dbo].[tb_Supplier]
        SET        SupplierName = @SupplierName,
                InBusinessSince = @InBusinessSince,
                TaxNumber = @TaxNumber,
                LicenseNumber = @LicenseNumber,
                IsActive = @IsActive,
                PreQualificationNumber = @PreQualificationNumber,
                PreQualificationDate = @PreQualificationDate,
                PreQualificationExpiryDate = @PreQualificationExpiryDate,
                UpdatedBy = @UserID,
                UpdatedOn = GETDATE()
        WHERE    SupplierID = @SupplierID;

        INSERT INTO
            [dbo].[tb_SupplierLog]
                (    Operation, SupplierID,
                    SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                    IsActive,
                    PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                    CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
        SELECT    'UPDATE', @SupplierID,
                SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                IsActive,
                PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
        FROM    [dbo].[tb_Supplier] WITH (NOLOCK)
        WHERE    SupplierID = @SupplierID;
    END
END

GO

CREATE PROCEDURE [dbo].[sp_Supplier_Delete]
    -- Add the parameters for the stored procedure here
    @SupplierID INT,
    @UserID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    INSERT INTO
        [dbo].[tb_SupplierLog]
            (    Operation, SupplierID,
                SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                IsActive,
                PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
    SELECT    'DELETE', @SupplierID,
            SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
            IsActive,
            PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
            CreatedBy, CreatedOn, @UserID, GETDATE()
    FROM    [dbo].[tb_Supplier] WITH (NOLOCK)
    WHERE    SupplierID = @SupplierID;

    DELETE FROM    [dbo].[tb_Supplier]
    WHERE        SupplierID = @SupplierID;
    
END

GO

Please note that in Insert, Update and Delete stored procedures we have the logging code as well. For this article logging code is implemented in stored procedure instead of using triggers. Some detail of the stored procedure is as follow:

For Insert, the inserted data is logged in the Log Table with Operation as Insert. For Update, the data is first logged in the Log Table with Operation as Update. In case of Delete the data is first saved in Log Table with Operation as Delete. This will serve as traces to locate the changes in data, who did it and when they were done.

Now comes the part when we want to revert the changes that was done in past.

A simple query can retrieve the data from Log Table with details that what change was done and so did it. Stored procedure is created using the same query. The result will contain complete data so that same can be used to restore data back to any particular state. SQL Script to create stored procedure is as follow:

SQL

CREATE PROCEDURE [dbo].[sp_SupplierLog_SelectBySupplierID]
    -- Add the parameters for the stored procedure here
    @SupplierID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT        *
    FROM        [dbo].[tb_SupplierLog] WITH (NOLOCK)
    WHERE        SupplierID = @SupplierID
    ORDER BY    [SupplierLogID];
    
END

GO

Now we have the details, we can simple restore the data to existing state by simply using the LogID. SQL Script for stored procedure is below:

SQL

CREATE PROCEDURE [dbo].[sp_Supplier_Restore]
    -- Add the parameters for the stored procedure here
    @SupplierLogID INT,
    @UserID INT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @SupplierID AS INT;
    
    SELECT        @SupplierID = ISNULL(MAX([Supplier].[SupplierID]), 0)
    FROM        [dbo].[tb_SupplierLog] [SupplierLog]
    INNER JOIN    [dbo].[tb_Supplier] [Supplier] ON [SupplierLog].[SupplierID] = [Supplier].[SupplierID]
    WHERE        [SupplierLogID] = @SupplierLogID

    IF (@SupplierID = 0)
    BEGIN
        -- INSERT DATA
        SET IDENTITY_INSERT [dbo].[tb_Supplier] ON;

        INSERT INTO
            [dbo].[tb_Supplier]
                (    SupplierID, SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                    IsActive,
                    PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                    CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
        SELECT        SupplierID, SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                    IsActive,
                    PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                    CreatedBy, CreatedOn, @UserID, GETDATE()
        FROM        [dbo].[tb_SupplierLog] [SupplierLog]
        WHERE        [SupplierLog].[SupplierLogID] = @SupplierLogID

        SET IDENTITY_INSERT [dbo].[tb_Supplier] OFF;

        SET @SupplierID = SCOPE_IDENTITY();
    END
    ELSE
    BEGIN
        -- UPDATE EXISTING RECORDS
        UPDATE        [dbo].[tb_Supplier]
        SET            SupplierName = [SupplierLog].[SupplierName],
                    InBusinessSince = [SupplierLog].[InBusinessSince],
                    TaxNumber = [SupplierLog].[TaxNumber],
                    LicenseNumber = [SupplierLog].[LicenseNumber],
                    IsActive = [SupplierLog].[IsActive],
                    PreQualificationNumber = [SupplierLog].[PreQualificationNumber],
                    PreQualificationDate = [SupplierLog].[PreQualificationDate],
                    PreQualificationExpiryDate = [SupplierLog].[PreQualificationExpiryDate],
                    UpdatedBy = @UserID,
                    UpdatedOn = GETDATE()
        FROM        [dbo].[tb_SupplierLog] [SupplierLog]
        INNER JOIN    [dbo].[tb_Supplier] [Supplier] ON [SupplierLog].[SupplierID] = [Supplier].[SupplierID]
        WHERE        [SupplierLog].[SupplierLogID] = @SupplierLogID
    END

    INSERT INTO
        [dbo].[tb_SupplierLog]
            (    Operation, SupplierID,
                SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
                IsActive,
                PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
                CreatedBy, CreatedOn, UpdatedBy, UpdatedOn)
    SELECT    'RESTORE', @SupplierID,
            SupplierName, InBusinessSince, TaxNumber, LicenseNumber,
            IsActive,
            PreQualificationNumber, PreQualificationDate, PreQualificationExpiryDate,
            CreatedBy, CreatedOn, UpdatedBy, UpdatedOn
    FROM    [dbo].[tb_Supplier] WITH (NOLOCK)
    WHERE    SupplierID = @SupplierID;

    SELECT    *
    FROM    [dbo].[tb_Supplier] WITH (NOLOCK)
    WHERE    [SupplierID] = @SupplierID;

END

GO

Sample

Please find attached .ZIP file to test this locally. For online demo, same has been uploaded. Please use the link: http://samplecode.aqeelarshad.com/swagger/ui/index

Conclusion

With the help of simple Change logging, tracking and reverting using SQL Server we can not only easily manage the data but also revert the data to existing state.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Aqeeel

Software Developer

Pakistan

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Change logging, tracking and reverting using SQL Server

Introduction

Background

Implementation

Sample

Conclusion

License

Comments and Discussions