Change logging, tracking and reverting using SQL Server
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
|
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,
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:
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:
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:
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.