Update Trigger to Store Only Updated Column and Former Value

  • Larry Schmidt-491187

    SSCrazy

    Points: 2553

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

  • dkorobov

    Grasshopper

    Points: 11

    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: 2553

    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: 2553

    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: 2553

    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

    END

  • Cary Hower-563110

    SSChasing Mays

    Points: 623

    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: 2553

    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

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

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