Update Trigger to Store Only Updated Column and Former Value

Question

Update Trigger to Store Only Updated Column and Former Value

Larry Schmidt-491187

SSCrazy

Points: 2908
More actions
December 15, 2012 at 3:19 pm

#271699

Comments posted to this topic are about the item Update Trigger to Store Only Updated Column and Former Value

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dkorobov Grasshopper Points: 11 More actions · Answer 1

I did not run the script but by the look of the functions used you are not getting update columns. You get column names included in the update statement so if you keep updating value with its current value the script will keep firing even though from the user standpoint there was no change. Is that right?

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 2

Yes, that would be correct. In this case, the requirements were to show a Field History, and if someone is changing the value in a column, and then perhaps changing it back to its original value during the same edit process, then the column has been updated, even though the value may not change. So, you're correct in saying that the former and current values are not compared. We simply know that the column has been updated.

The aim of this script was to avoid saving an entire row from the table, which some triggers do and is much easier, merely because one column may have been changed. In that case, a "Field History" would have shown multiple instances of the same value, even though that column wasn't even touched.

One very expensive alternative would have been to add a Last Modified timestamp associated with every column, but the main table in this application is approaching 100 columns, and the overhead was considered too extreme.

Thanks for your comment!

Larry

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 3

Actually, to continue my response, you are correct in saying that an app, if it does a "blanket" UPDATE to all columns, will create history table rows, regardless of whether an individual column has changed.

This is why an earlier (ASP.Net) web app I wrote was required to compare column values to see if they'd changed, and then dynamically prepare an UPDATE to only those columns that had value changes. This places a bit more burden on the app builder, but if you truly want to capture individual column changes, it's necessary.

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 4

I've reworked the script to create History table rows only for columns that have actually changed in value. Here is the revised version:

/****** Object: Trigger [dbo].[upd_trg_SCD_Project] Script Date: 12/16/2012 10:58:03 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;

SELECT * into #tmpUpdate from inserted;

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), @NewVal varchar(max), @ColName varchar(max), @sql nvarchar(max)

declare @tmptmp table (Val varchar(max))

declare @tmptmp2 table (NewVal 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 @tmptmp2 exec ('select top (1) ' + @ColName + ' AS NewVal from #tmpUpdate')

select @NewVal = NewVal from @tmptmp2

if @val <> @NewVal 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

Cary Hower-563110 SSChasing Mays Points: 652 More actions · Answer 5

FYI, this trigger will fail if more than one row is updated with the update query because a select into a scalar variable cannot result in more than one value.

Larry Schmidt-491187 SSCrazy Points: 2908 More actions · Answer 6

True, but I should have given more details about the application this was designed for. It is a project management application, in which a project manager selects a project that he is the owner of, can edit various dates, milestones, project attributes, etc., and then either save (one row) or cancel. There will not be an instance where an UPDATE is applied to more than one row.

Larry