SQL History Table – Copy row to another table before update

This is great! I was looking for something like this, but could not find anything: how does that saying go – ‘necessity is the mother of invention’…???

This script builds a trigger that will copy a row as it was before an update to a history table that it will build if such a table does not exist.

CREATE TRIGGER [dbo].[<TriggerName,,>]
ON [dbo].[<YourTableName,,>]
FOR UPDATE
AS
DECLARE @TableName varchar(50), @KeyColName varchar(50)
----------------------------------------------------------
-- Warren Rapson Jan 2009
--
-- This trigger is designed to copy a record from a table
-- into its history table as it was before a change was
-- made.
--
-- The trigger should create a History table it the form
-- MyExistingTableName_History.
--
-- You must enter the name of the table to be used and its
-- key column in the below variables as well as the
-- trigger details at the top - press Ctrl + Shift + M
---------------------------------------------------------
   SET @TableName = '<YourTableName,,>'
   SET @KeyColName = '<YourTableKeyColName,,>'
---------------------------------------------------------

DECLARE
   @ColName nvarchar(4000)
   ,@AllCols nvarchar(4000)
   ,@OldValCols nvarchar(4000)
   ,@HistoryTableName nvarchar(50)
   ,@SQL nvarchar(1000)
   ,@SetStatement nvarchar(4000)
   ,@InsertHisoryStatement nvarchar(4000)

SET @AllCols = ''
SET @OldValCols = ''
SET @SetStatement = ''
SET @HistoryTableName = @TableName + '_History'

-- make history table if it does not exist
IF Object_ID(@HistoryTableName) IS NULL
BEGIN
   SELECT @SQL = 'SELECT * INTO ' + @HistoryTableName + ' FROM ' + @TableName + ' Where 1 = 2'
   EXEC (@SQL)
   -- add history columns
   SELECT @SQL = 'ALTER TABLE ' + @HistoryTableName
               + ' ADD HistoryDateTime datetime DEFAULT GETDATE(), HistoryUsername varchar(50) DEFAULT suser_sname()'
   EXEC (@SQL)
END

-- get coloum names from your table
DECLARE mycursor CURSOR FOR
   SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION
OPEN mycursor
   FETCH NEXT FROM mycursor INTO @ColName
   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @AllCols = @AllCols + @ColName + ', '
      SET @OldValCols = @OldValCols + 'Old.' + @ColName + ', '
      SET @SetStatement = @SetStatement + @ColName + ' = Ins.' + @ColName + ', '
      FETCH NEXT FROM mycursor INTO @ColName
   END
CLOSE mycursor
DEALLOCATE mycursor 

-- remove last commas from statements
SET @AllCols = SUBSTRING(@AllCols, 1, LEN(@AllCols)-1)
SET @OldValCols = SUBSTRING(@OldValCols, 1, LEN(@OldValCols)-1)
SET @SetStatement = SUBSTRING(@SetStatement, 1, LEN(@SetStatement)-1)
 
-- this stops the 'invalid object name' error re the 'deleted' table - i don't know why...
IF Object_ID('tempdb..#tmp_OldVals') IS NOT NULL DROP TABLE #tmp_OldVals
SELECT * INTO #tmp_OldVals FROM deleted
 
SELECT @InsertHisoryStatement = 'INSERT ' + @TableName + '_HISTORY (' + @AllCols + ')
                                 SELECT ' + @OldValCols + '
                                 FROM #tmp_OldVals as Old, ' + @TableName + '
                                 WHERE Old.' + @KeyColName + ' = ' + @TableName + '.' + @KeyColName
EXEC(@InsertHisoryStatement)