Update Trigger to Store Only Updated Column and Former Value

Larry Schmidt, 2012-12-17 (first published: 2012-12-04)

The SCD_Project table stores data about IT projects, and the SCD_History table is an audit reference, in which is stored a row for any change made to a specific column in several other tables. For that reason, the History table has columns for the source table, column, rowlink (primary key of that table), and value prior to update. The LastModifiedDate and LastModifier (11-character employee ID) are columns common to all tables which have triggers that write to the History table. In addition, using a cursor was avoided by adding a RowID (identity) and stepping through the changed columns. The changed columns and their names are obtained through the use of the sys.fn_IsBitSetInBitmask function.

/****** Object: Trigger [dbo].[upd_trg_SCD_Project] Script Date: 12/4/2012 8:28:06 AM ******/SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='TR' AND name='upd_trg_SCD_Project') DROP TRIGGER upd_trg_SCD_Project GO -- ============================================= -- Author:Larry Schmidt -- Create date: 11/30/2012 -- Description:Trigger On Updated Columns, --Write Results to History Table -- ============================================= CREATE TRIGGER [dbo].[upd_trg_SCD_Project] ON [dbo].[SCD_Project] FOR UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @ProjID int, @LastModifiedDate smalldatetime, @LastModifier varchar(11) DECLARE @FldsUpdated varchar(max) DECLARE @ColumnsUpdated VARBINARY(100) SET @ColumnsUpdated = COLUMNS_UPDATED() SELECT * into #tmpExisting from deleted; SET @ProjID = (select ProjID from #tmpExisting) SET @LastModifiedDate = (select LastModifiedDate from #tmpExisting) SET @LastModifier = (select LastModifier from #tmpExisting) (SELECT 'SCD_Project' AS HistTbl,COLUMN_NAME AS HistCol,'Old Contents' AS HistVal into #tmpTrigger FROM INFORMATION_SCHEMA.COLUMNS Field WHERE TABLE_NAME = 'SCD_Project' AND sys.fn_IsBitSetInBitmask(@ColumnsUpdated,COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') ) <> 0); ALTER TABLE #tmpTrigger ADD tmpID [int] IDENTITY (1,1) declare @Counter int, @nRows int, @Val varchar(max), @ColName varchar(max), @sql nvarchar(max) declare @tmptmp table (Val varchar(max)) set @Counter = 1 set @nRows = (select count(*) from #tmpTrigger) WHILE @Counter <= @nRows BEGIN set @ColName = (Select HistCol from #tmpTrigger where tmpID = @Counter) insert @tmptmp exec ('select top (1) ' + @ColName + ' AS Val from #tmpExisting') select @Val = Val from @tmptmp INSERT into SCD_History (TableName, ColumnName, RowLink, FormerValue, LastModifiedDate, LastModifier) Select HistTbl, HistCol, @ProjID, @Val, @LastModifiedDate, @LastModifier from #tmpTrigger where tmpID = @Counter set @Counter = @Counter + 1 END END GO