See if my trigger will help you... it's using the Tally Table, SYSCOLUMNS, and the COLUMNS_UPDATED...
The WHERE CLAUSE are the Columns I want to capture data for, and my CASE STATEMENTS MATCH...
I use this trigger on 20 tables... Some have 30 columns... so being that I am lazy and that changes can happen a lot, I open the table I want like I'm going to edit it, paste that into an excel spreadsheet... I give my columns names a NAMED RANGE, and in vba colde it produces all these for me in milliseconds...
This Trigger is super fast using the combination mention above, all built from the help of this Forum and articles... yah....
ALTER TRIGGER [dbo].[trg_tlkpSegmentGroupSource_AuditUpdates]
ON [dbo].[tlkpSegmentGroupSource]
--Author: John Steinbeck
--Date: 24 Sept 08
--Purpose: This Trigger is designed to capture any column whose value is updated... you will specify what columns you want
FOR UPDATE
AS
---- Declarations
DECLARE @TableName VARCHAR(100)
DECLARE @KeyField VARCHAR(100)
DECLARE @KeyVal VARCHAR(100)
DECLARE @OldVal VARCHAR(500)
DECLARE @NewVal VARCHAR(500)
DECLARE @COL_NAME NVARCHAR(100)
DECLARE @SYSUSER VARCHAR(100)
DECLARE @getdate-2 DATETIME
DECLARE @XID VARCHAR(50)
---- Instantiate
SET @TableName = 'tlkpSegmentGroupSource' --Table Name of the Table used for this Trigger CHANGE MY VALUE
SET @KeyField = 'SegmentGroupSourceID'
SELECT @KeyVal = SegmentGroupSourceID FROM INSERTED --CHANGE MY VALUE
SET @getdate-2 = CAST(GETDATE() AS DATETIME)
SET @XID= SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER)+1,(LEN(SYSTEM_USER)+1) - CHARINDEX('\',SYSTEM_USER))
SELECT @SYSUSER = USERNAME FROM tblAuthorizations
WHERE XID = @XID
SET @SYSUSER = ISNULL(@SYSUSER, @XID)
-- FOR INSERT AND UPDATE ONLY...
---- Update Audit Fields on Table
Update tlkpSegmentGroupSource
SET UpdatedBy = @SYSUSER, LastUpdate = @getdate-2
WHERE SegmentGroupSourceID IN (SELECT SegmentGroupSourceID FROM Inserted)
-- LOOPING THRU SYSTEM FUNCTION TO SEE WHAT COLUMNS HAVE BEEN UPDATED AND GET THE NAMES... STORE IN TEMP TBL
INSERT INTO tblAuditLog
(TableName, KeyField, KeyValue, UpdatedBy, LastUpdate, ChangedColumn, OldValue, NewValue)
SELECT
@TableName AS TBL
, @KeyField AS KY
, @KeyVal AS KV
, @SYSUSER AS SYS
, @getdate-2 AS THEDATE
, OLD.NAME AS COLNAME
, ISNULL(CAST(OLD.OLD_COL_VALUES AS VARCHAR(500)),'') AS OLD
, ISNULL(CAST(NEW.NEW_COL_VALUES AS VARCHAR(500)),'') AS NEW
FROM (
SELECT
COLS.N
, (CASE COLS.N
WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
END) OLD_COL_VALUES
, COLS.NAME
FROM
(
SELECT DISTINCT *
FROM DELETED
[Wink] D
CROSS Join
(
SELECT
S.NAME, T.N AS N
FROM
DBO.TALLY T INNER JOIN SYSCOLUMNS S
ON T.N = S.COLID
WHERE ((N IN (2, 3, 4, 5, 6)) -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
AND ID = object_id(@TableName))
AND (CASE
WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
THEN 1
ELSE 0
End
[Wink] = 1
[Wink] COLS
[Wink] OLD INNER JOIN
(
SELECT
COLS.N
, (CASE COLS.N
WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
END) NEW_COL_VALUES
, COLS.NAME
FROM
(
SELECT DISTINCT *
FROM INSERTED
[Wink] I
CROSS Join
(
SELECT
S.NAME, T.N AS N
FROM
DBO.TALLY T INNER JOIN SYSCOLUMNS S
ON T.N = S.COLID
WHERE ((N IN (2, 3, 4, 5, 6)) -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
AND ID = object_id(@TableName))
AND (CASE
WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
THEN 1
ELSE 0
End
[Wink] = 1
[Wink] COLS
[Wink] NEW
ON OLD.N = NEW.N