Update Trigger to Store Only Updated Column and Former Value

,

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

Rate

1.5 (2)

Share

Share

Rate

1.5 (2)