SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Update Trigger to Store Only Updated Column and Former Value


Update Trigger to Store Only Updated Column and Former Value

Author
Message
Larry Schmidt-491187
Larry Schmidt-491187
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 427
Comments posted to this topic are about the item Update Trigger to Store Only Updated Column and Former Value
dkorobov
dkorobov
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 38
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
Larry Schmidt-491187
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 427
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
Larry Schmidt-491187
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 427
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
Larry Schmidt-491187
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 427
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
Cary Hower-563110
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 162
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
Larry Schmidt-491187
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1644 Visits: 427
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search