Easy Database Table Change History

Introduction

This tip provides a simple method for collecting history on data changes. The ultimate goal is to allow historical data to be captured via a reusable method (procedure). This tip outlines a single stored procedure which works across multiple tables within a single or multiple databases.

Background

If you have ever been frustrated trying to capture history of your user’s changes, this is the tip for you. This method involves dynamic SQL, sys objects and a little creativity.

Using the Code

This method involves a single procedure call "AnyTableHistory" which can create a table and add a historical record to it.

First, you will need to create a table and add some data or use an existing table with data.

/*
** create a table and add several records
*/
CREATE TABLE [dbo].[Users](
         [UserId] [int] IDENTITY(100,1) NOT NULL,
         [UserName] [nvarchar](50) NOT NULL,
         [FirstName] [nvarchar](150) NULL,
         [LastName] [nvarchar](250) NULL,
         [CreatedDate] [datetime] NULL,
         [CreatedUserId] [int] NULL
  CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
         [UserId] ASC
)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_CreatedDate]  DEFAULT (getdate()) 
FOR [CreatedDate]
GO

INSERT INTO [dbo].[Users]([UserName], [FirstName], [LastName], [CreatedUserId])
     VALUES ('Admin', 'Admin','User', NULL)
GO

INSERT INTO [dbo].[Users]([UserName], [FirstName], [LastName], [CreatedUserId])
     VALUES ('NeedHistory', 'Need','History', 100)
GO


INSERT INTO [dbo].[Users]([UserName], [FirstName], [LastName], [CreatedUserId])
     VALUES ('AnotherUser', 'User1','OfAnother', 100)
GO

Next, you need to execute the attached SQL file.

Now, you can call the stored procedure as follows…

• @PKColName - The primary key column name (optional)
• @PKValue - Primary key value of the history record
• @TableName - Name of the table
• @HstryUserName - Name of the user updating the record
• @CreateHistoryTbl - Optional parameter for creating the history table
• @AddMIssingFields - Optional parameter for syncing all columns from the parent table

-- create the history table if it does not exits
exec AnyTableHistoryHandler null, '100', 'Users', null, 1, 0

-- execute against an existing table
exec AnyTableHistoryHandler null, '100', 'Users', null, 0, 0

How It Works…

• If the primary key is not provided, fetch it from the information schema.

  IF(@PKColName is null) -- get the pk from the schema table
  BEGIN
      SELECT @PKSQL = COLUMN_NAME
      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
      WHERE Objectproperty(Object_id(constraint_name), _
      'IsPrimaryKey') = 1 AND table_name = @TableName        
  END

• Get the data type of the primary key as it will be needed later.

  SELECT @PKColType = DATA_TYPE
    FROM INFORMATION_SCHEMA.COLUMNS
   WHERE table_name = @TableName and COLUMN_NAME = @PKColName

• Create the history table if it does not exist. All fields are set to NULLABLE.

  --Also adds history related fields
  IF(@CreateHistoryTbl = 1 AND NOT EXISTS(SELECT *
                                          FROM INFORMATION_SCHEMA.COLUMNS 
                                          WHERE table_name = @TableName + @HistoryNm))
  BEGIN
           DECLARE @HstTableSQL NVARCHAR(MAX) = ''
           SELECT @HstTableSQL = COALESCE(@HstTableSQL + ', ', '') +  _
           (COLUMN_NAME + ' ' + DATA_TYPE + Case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL _
           then '(' + Cast(CHARACTER_MAXIMUM_LENGTH as nvarchar) + ')' ELSE ' '  end + ' NULL ')
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE table_name = @TableName
  
           SET @HstTableSQL = 'CREATE TABLE ' + @TableName + @HistoryNm +
                   ' ( HistoryDate DATETIME NULL DEFAULT(GETDATE()), ' +
                   ' HistoryUser VARCHAR(150) NOT NULL DEFAULT(SYSTEM_USER)' + @HstTableSQL + ')'
           EXEC sp_executesql @HstTableSQL
  END

• Retrieve a list of columns from the history table. This is done to handle avoid errors with columns not in the history table

  SELECT @coListStr = COALESCE(@coListStr + ',', '') + COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS IC
  WHERE TABLE_NAME = @TableName  + @HistoryNm AND COLUMN_NAME NOT IN ('HistoryDate','HistoryUser')
  SET @coListStr = Substring(@coListStr, 2, Len(@coListStr) - 1) --remove the leading comma

• Handle the missing fields if the parameter is passed

  IF(@AddMIssingFields = 1)
  BEGIN
           DECLARE @missingCols NVARCHAR(MAX) = '' -- get a list of missing columns and include in table creation
  
           SELECT @missingCols = COALESCE(@missingCols + ', ', '') +  _
           (COLUMN_NAME + ' ' + DATA_TYPE + Case when CHARACTER_MAXIMUM_LENGTH IS NOT NULL _
           then '(' + Cast(CHARACTER_MAXIMUM_LENGTH as nvarchar) + ')' ELSE ' '  end + ' NULL ')
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = @TableName AND COLUMN_NAME NOT IN _
           (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS _
           WHERE table_name = @TableName + @HistoryNm)
  
          IF(LEN(@missingCols) > 0)
          BEGIN
             SET @missingCols = 'ALTER TABLE ' + @TableName + @HistoryNm + ' ADD ' + @missingCols
             SET @missingCols = (SELECT REPLACE(@missingCols, 'ADD ,',  'ADD ' ))
  
              EXEC sp_executesql @missingCols
           END
  END

• Generate insert statement with and execute the insert:

  DECLARE @SQL NVARCHAR(MAX) = 'INSERT INTO [dbo].' +  _
  @TableName + @HistoryNm + ' (' + @coListStr + ')'
  
  SET @SQL = @SQL + ' SELECT ' + @coListStr
                  + ' FROM [dbo].' + @TableName
                  + ' WHERE ' + @PKColName + ' = ' 
                  + CASE WHEN @PKColType in _
                  ('bigint','numeric','smallint','int','decimal','smallmoney','tinyint')
                   THEN @PKValue ELSE '''' +@PKValue + '''' END
  
  EXEC sp_executesql @SQL

Points of Interest

This stored procedure can be executed using a trigger or by calling prior to making an update. Here is a sample of how to handle history using a trigger.

• Create an “INSTEAD OF UPDATE” trigger, which fires before the update is applied:

  CREATE TRIGGER dbo.Users_CaptureHistoryTRGG
  ON dbo.Users
  INSTEAD OF UPDATE
  
  AS
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
        SET NOCOUNT ON;
        DECLARE @ID INT = (SELECT UserId FROM   inserted)
        EXEC dbo.Anytablehistoryhandler 'UserId', @ID, 'Users'
  
        UPDATE dbo.Users
  
        SET    Users.UserName = COALESCE(i.UserName, Users.UserName),
               Users.FirstName = COALESCE(i.FirstName, Users.FirstName),
               Users.LastName = COALESCE(i.LastName, Users.LastName)
        FROM   inserted i
        WHERE  dbo.Users.UserId = i.UserId
  END

  Please note: “COALESCE” is used to ensure only updated values are set.

• Execute an update to any field on the table:

  UPDATE Users
  SET    lastname = 'updatedNm'
  WHERE  UserId = 101
  
  UPDATE Users
  SET    lastname = 'againNm'
  WHERE  UserId = 101

• View the records in the history table:

  SELECT * FROM UsersHistory

Hope this is useful.