There is a much simpler way of doing this and it also allows for multiple multi-row actions, the COLUMNS_UPDATED makes that difficult (tip: if there is a variable in the trigger used for the output, most likely its a one-row-only trigger). Here is a simple example which uses NULLIF to compare the old and the new value, ignoring the column if the values are the same.

😎

USE tempdb;

GO

SET NOCOUNT ON;

CREATE TABLE dbo.UpdateTriggerTest

(

UTT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_UTT_ID PRIMARY KEY CLUSTERED

,UTT_VALUE_1 INT NOT NULL

,UTT_VALUE_2 INT NOT NULL

,UTT_VALUE_3 INT NULL

,UTT_VALUE_4 INT NULL

,UTT_VALUE_5 INT NULL

);

CREATE TABLE dbo.UTT_AUDIT

(

UTT_AUDIT_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_UTT_AUDIT_ID PRIMARY KEY CLUSTERED

,UTT_AUDIT_DATE DATETIME2(7) NOT NULL CONSTRAINT DFLT_DBO_UTT_AUDIT_UTT_AUDIT_DATE DEFAULT(SYSDATETIME())

,UTT_ID INT NOT NULL

,UTT_ACTION_XML XML NOT NULL

);

GO

CREATE TRIGGER dbo.TRG_CATCH_UPDATE_UpdateTriggerTest

ON dbo.UpdateTriggerTest

/* Catch any changes to the UTT_VALUE_x columns */

AFTER UPDATE,DELETE

AS

IF (SELECT COUNT(*) FROM inserted) > 0 AND (SELECT COUNT(*) FROM deleted) > 0

BEGIN

-- UPDATE

INSERT INTO dbo.UTT_AUDIT (UTT_ID,UTT_ACTION_XML)

SELECT

DD.UTT_ID

,(

SELECT

'UPDATE' AS '@COL_ACTION'

,NULLIF(D.UTT_VALUE_1,I.UTT_VALUE_1) AS 'UTT_VALUE_1/@OLD_VALUE'

,NULLIF(I.UTT_VALUE_1,D.UTT_VALUE_1) AS 'UTT_VALUE_1/@NEW_VALUE'

,NULLIF(D.UTT_VALUE_2,I.UTT_VALUE_2) AS 'UTT_VALUE_2/@OLD_VALUE'

,NULLIF(I.UTT_VALUE_2,D.UTT_VALUE_2) AS 'UTT_VALUE_2/@NEW_VALUE'

,NULLIF(D.UTT_VALUE_3,I.UTT_VALUE_3) AS 'UTT_VALUE_3/@OLD_VALUE'

,NULLIF(I.UTT_VALUE_3,D.UTT_VALUE_3) AS 'UTT_VALUE_3/@NEW_VALUE'

,NULLIF(D.UTT_VALUE_4,I.UTT_VALUE_4) AS 'UTT_VALUE_4/@OLD_VALUE'

,NULLIF(I.UTT_VALUE_4,D.UTT_VALUE_4) AS 'UTT_VALUE_4/@NEW_VALUE'

,NULLIF(D.UTT_VALUE_5,I.UTT_VALUE_5) AS 'UTT_VALUE_5/@OLD_VALUE'

,NULLIF(I.UTT_VALUE_5,D.UTT_VALUE_5) AS 'UTT_VALUE_5/@NEW_VALUE'

FROM deleted D

LEFT OUTER JOIN inserted I

ON D.UTT_ID = I.UTT_ID

WHERE DD.UTT_ID = D.UTT_ID

FOR XML PATH('COL_CHANGE'), TYPE

) AS UTT_XML

FROM deleted DD

END

ELSE IF (SELECT COUNT(*) FROM inserted) = 0 AND (SELECT COUNT(*) FROM deleted) > 0

BEGIN

-- DELETE

INSERT INTO dbo.UTT_AUDIT (UTT_ID,UTT_ACTION_XML)

SELECT

DD.UTT_ID

,(

SELECT

'DELETE' AS '@COL_ACTION'

,D.UTT_VALUE_1 AS 'UTT_VALUE_1/@OLD_VALUE'

,D.UTT_VALUE_2 AS 'UTT_VALUE_2/@OLD_VALUE'

,D.UTT_VALUE_3 AS 'UTT_VALUE_3/@OLD_VALUE'

,D.UTT_VALUE_4 AS 'UTT_VALUE_4/@OLD_VALUE'

,D.UTT_VALUE_5 AS 'UTT_VALUE_5/@OLD_VALUE'

FROM deleted D

WHERE DD.UTT_ID = D.UTT_ID

FOR XML PATH('COL_CHANGE'), TYPE

) AS UTT_XML

FROM deleted DD

END

GO

/* INSERT TEST DATA */

INSERT INTO dbo.UpdateTriggerTest

(

UTT_VALUE_1

,UTT_VALUE_2

,UTT_VALUE_3

,UTT_VALUE_4

,UTT_VALUE_5

)

VALUES (1,1,1,1,1)

,(2,2,2,2,2)

,(3,3,3,3,3)

,(4,4,4,4,4)

,(5,5,5,5,5)

,(6,6,6,6,6);

/* UPDATE ONE COLUMN */

UPDATE dbo.UpdateTriggerTest

SET UTT_VALUE_1 = UTT_VALUE_1 + 1

/* UPDATE TWO COLUMNS */

UPDATE dbo.UpdateTriggerTest

SET UTT_VALUE_1 = UTT_VALUE_1 + 1

,UTT_VALUE_2 = UTT_VALUE_2 + 2

/* UPDATE ALL COLUMNS */

UPDATE dbo.UpdateTriggerTest

SET UTT_VALUE_1 = UTT_VALUE_1 + 1

,UTT_VALUE_2 = UTT_VALUE_2 + 2

,UTT_VALUE_3 = UTT_VALUE_3 + 3

,UTT_VALUE_4 = UTT_VALUE_4 + 4

,UTT_VALUE_5 = UTT_VALUE_5 + 5

/* DELETE EVERY THIRD ROW */

DELETE FROM dbo.UpdateTriggerTest

WHERE UTT_ID % 3 = 2

/* DELETE THE REMAINING ROWS */

DELETE FROM dbo.UpdateTriggerTest

/* INSPECT THE AUDIT */

SELECT * FROM dbo.UTT_AUDIT;

/* CLEAN UP */

DROP TABLE dbo.UpdateTriggerTest;

DROP TABLE dbo.UTT_AUDIT;

Results

UTT_AUDIT_ID UTT_AUDIT_DATE UTT_ID UTT_ACTION_XML

------------ --------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

1 2014-07-27 08:18:49.8765516 6 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="6" NEW_VALUE="7" /></COL_CHANGE>

2 2014-07-27 08:18:49.8765516 5 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="5" NEW_VALUE="6" /></COL_CHANGE>

3 2014-07-27 08:18:49.8765516 4 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="4" NEW_VALUE="5" /></COL_CHANGE>

4 2014-07-27 08:18:49.8765516 3 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="3" NEW_VALUE="4" /></COL_CHANGE>

5 2014-07-27 08:18:49.8765516 2 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="2" NEW_VALUE="3" /></COL_CHANGE>

6 2014-07-27 08:18:49.8765516 1 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="1" NEW_VALUE="2" /></COL_CHANGE>

7 2014-07-27 08:18:49.8825519 6 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="7" NEW_VALUE="8" /><UTT_VALUE_2 OLD_VALUE="6" NEW_VALUE="8" /></COL_CHANGE>

8 2014-07-27 08:18:49.8825519 5 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="6" NEW_VALUE="7" /><UTT_VALUE_2 OLD_VALUE="5" NEW_VALUE="7" /></COL_CHANGE>

9 2014-07-27 08:18:49.8825519 4 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="5" NEW_VALUE="6" /><UTT_VALUE_2 OLD_VALUE="4" NEW_VALUE="6" /></COL_CHANGE>

10 2014-07-27 08:18:49.8825519 3 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="4" NEW_VALUE="5" /><UTT_VALUE_2 OLD_VALUE="3" NEW_VALUE="5" /></COL_CHANGE>

11 2014-07-27 08:18:49.8825519 2 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="3" NEW_VALUE="4" /><UTT_VALUE_2 OLD_VALUE="2" NEW_VALUE="4" /></COL_CHANGE>

12 2014-07-27 08:18:49.8825519 1 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="2" NEW_VALUE="3" /><UTT_VALUE_2 OLD_VALUE="1" NEW_VALUE="3" /></COL_CHANGE>

13 2014-07-27 08:18:49.8865521 6 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="8" NEW_VALUE="9" /><UTT_VALUE_2 OLD_VALUE="8" NEW_VALUE="10" /><UTT_VALUE_3 OLD_VALUE="6" NEW_VALUE="9" /><UTT_VALUE_4 OLD_VALUE="6" NEW_VALUE="10" /><UTT_VALUE_5 OLD_VALUE="6" NEW_VALUE="11" /></COL_

14 2014-07-27 08:18:49.8865521 5 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="7" NEW_VALUE="8" /><UTT_VALUE_2 OLD_VALUE="7" NEW_VALUE="9" /><UTT_VALUE_3 OLD_VALUE="5" NEW_VALUE="8" /><UTT_VALUE_4 OLD_VALUE="5" NEW_VALUE="9" /><UTT_VALUE_5 OLD_VALUE="5" NEW_VALUE="10" /></COL_CH

15 2014-07-27 08:18:49.8865521 4 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="6" NEW_VALUE="7" /><UTT_VALUE_2 OLD_VALUE="6" NEW_VALUE="8" /><UTT_VALUE_3 OLD_VALUE="4" NEW_VALUE="7" /><UTT_VALUE_4 OLD_VALUE="4" NEW_VALUE="8" /><UTT_VALUE_5 OLD_VALUE="4" NEW_VALUE="9" /></COL_CHA

16 2014-07-27 08:18:49.8865521 3 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="5" NEW_VALUE="6" /><UTT_VALUE_2 OLD_VALUE="5" NEW_VALUE="7" /><UTT_VALUE_3 OLD_VALUE="3" NEW_VALUE="6" /><UTT_VALUE_4 OLD_VALUE="3" NEW_VALUE="7" /><UTT_VALUE_5 OLD_VALUE="3" NEW_VALUE="8" /></COL_CHA

17 2014-07-27 08:18:49.8865521 2 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="4" NEW_VALUE="5" /><UTT_VALUE_2 OLD_VALUE="4" NEW_VALUE="6" /><UTT_VALUE_3 OLD_VALUE="2" NEW_VALUE="5" /><UTT_VALUE_4 OLD_VALUE="2" NEW_VALUE="6" /><UTT_VALUE_5 OLD_VALUE="2" NEW_VALUE="7" /></COL_CHA

18 2014-07-27 08:18:49.8865521 1 <COL_CHANGE COL_ACTION="UPDATE"><UTT_VALUE_1 OLD_VALUE="3" NEW_VALUE="4" /><UTT_VALUE_2 OLD_VALUE="3" NEW_VALUE="5" /><UTT_VALUE_3 OLD_VALUE="1" NEW_VALUE="4" /><UTT_VALUE_4 OLD_VALUE="1" NEW_VALUE="5" /><UTT_VALUE_5 OLD_VALUE="1" NEW_VALUE="6" /></COL_CHA

19 2014-07-27 08:18:49.8905524 5 <COL_CHANGE COL_ACTION="DELETE"><UTT_VALUE_1 OLD_VALUE="8" /><UTT_VALUE_2 OLD_VALUE="9" /><UTT_VALUE_3 OLD_VALUE="8" /><UTT_VALUE_4 OLD_VALUE="9" /><UTT_VALUE_5 OLD_VALUE="10" /></COL_CHANGE>

20 2014-07-27 08:18:49.8905524 2 <COL_CHANGE COL_ACTION="DELETE"><UTT_VALUE_1 OLD_VALUE="5" /><UTT_VALUE_2 OLD_VALUE="6" /><UTT_VALUE_3 OLD_VALUE="5" /><UTT_VALUE_4 OLD_VALUE="6" /><UTT_VALUE_5 OLD_VALUE="7" /></COL_CHANGE>

21 2014-07-27 08:18:49.8925525 6 <COL_CHANGE COL_ACTION="DELETE"><UTT_VALUE_1 OLD_VALUE="9" /><UTT_VALUE_2 OLD_VALUE="10" /><UTT_VALUE_3 OLD_VALUE="9" /><UTT_VALUE_4 OLD_VALUE="10" /><UTT_VALUE_5 OLD_VALUE="11" /></COL_CHANGE>

22 2014-07-27 08:18:49.8925525 4 <COL_CHANGE COL_ACTION="DELETE"><UTT_VALUE_1 OLD_VALUE="7" /><UTT_VALUE_2 OLD_VALUE="8" /><UTT_VALUE_3 OLD_VALUE="7" /><UTT_VALUE_4 OLD_VALUE="8" /><UTT_VALUE_5 OLD_VALUE="9" /></COL_CHANGE>

23 2014-07-27 08:18:49.8925525 3 <COL_CHANGE COL_ACTION="DELETE"><UTT_VALUE_1 OLD_VALUE="6" /><UTT_VALUE_2 OLD_VALUE="7" /><UTT_VALUE_3 OLD_VALUE="6" /><UTT_VALUE_4 OLD_VALUE="7" /><UTT_VALUE_5 OLD_VALUE="8" /></COL_CHANGE>

24 2014-07-27 08:18:49.8925525 1 <COL_CHANGE COL_ACTION="DELETE"><UTT_VALUE_1 OLD_VALUE="4" /><UTT_VALUE_2 OLD_VALUE="5" /><UTT_VALUE_3 OLD_VALUE="4" /><UTT_VALUE_4 OLD_VALUE="5" /><UTT_VALUE_5 OLD_VALUE="6" /></COL_CHANGE>

For completeness, here is a query to get the details from the audit table

SELECT

A.UTT_AUDIT_ID

,A.UTT_AUDIT_DATE

,A.UTT_ID

,ROW_NUMBER() OVER (PARTITION BY A.UTT_ID ORDER BY A.UTT_AUDIT_DATE) AS COL_HIST_RID

,COL.CHANGE.value('@COL_ACTION','VARCHAR(6)') AS COL_ACTION

,COL.CHANGE.value('UTT_VALUE_1[1]/@OLD_VALUE','INT') AS UTT_VALUE_1_OLD

,COL.CHANGE.value('UTT_VALUE_1[1]/@NEW_VALUE','INT') AS UTT_VALUE_1_NEW

,COL.CHANGE.value('UTT_VALUE_2[1]/@OLD_VALUE','INT') AS UTT_VALUE_2_OLD

,COL.CHANGE.value('UTT_VALUE_2[1]/@NEW_VALUE','INT') AS UTT_VALUE_2_NEW

,COL.CHANGE.value('UTT_VALUE_3[1]/@OLD_VALUE','INT') AS UTT_VALUE_3_OLD

,COL.CHANGE.value('UTT_VALUE_3[1]/@NEW_VALUE','INT') AS UTT_VALUE_3_NEW

,COL.CHANGE.value('UTT_VALUE_4[1]/@OLD_VALUE','INT') AS UTT_VALUE_4_OLD

,COL.CHANGE.value('UTT_VALUE_4[1]/@NEW_VALUE','INT') AS UTT_VALUE_4_NEW

,COL.CHANGE.value('UTT_VALUE_5[1]/@OLD_VALUE','INT') AS UTT_VALUE_5_OLD

,COL.CHANGE.value('UTT_VALUE_5[1]/@NEW_VALUE','INT') AS UTT_VALUE_5_NEW

FROM dbo.UTT_AUDIT A

OUTER APPLY A.UTT_ACTION_XML.nodes('COL_CHANGE') AS COL(CHANGE)

Results

UTT_AUDIT_ID UTT_AUDIT_DATE UTT_ID COL_HIST_RID COL_ACTION UTT_VALUE_1_OLD UTT_VALUE_1_NEW UTT_VALUE_2_OLD UTT_VALUE_2_NEW UTT_VALUE_3_OLD UTT_VALUE_3_NEW UTT_VALUE_4_OLD UTT_VALUE_4_NEW UTT_VALUE_5_OLD UTT_VALUE_5_NEW

------------ --------------------------- ----------- -------------------- ---------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- --------------- ---------------

6 2014-07-27 08:38:25.2857811 1 1 UPDATE 1 2 NULL NULL NULL NULL NULL NULL NULL NULL

12 2014-07-27 08:38:25.2907814 1 2 UPDATE 2 3 1 3 NULL NULL NULL NULL NULL NULL

18 2014-07-27 08:38:25.2947816 1 3 UPDATE 3 4 3 5 1 4 1 5 1 6

24 2014-07-27 08:38:25.3017820 1 4 DELETE 4 NULL 5 NULL 4 NULL 5 NULL 6 NULL

5 2014-07-27 08:38:25.2857811 2 1 UPDATE 2 3 NULL NULL NULL NULL NULL NULL NULL NULL

11 2014-07-27 08:38:25.2907814 2 2 UPDATE 3 4 2 4 NULL NULL NULL NULL NULL NULL

17 2014-07-27 08:38:25.2947816 2 3 UPDATE 4 5 4 6 2 5 2 6 2 7

20 2014-07-27 08:38:25.2987819 2 4 DELETE 5 NULL 6 NULL 5 NULL 6 NULL 7 NULL

4 2014-07-27 08:38:25.2857811 3 1 UPDATE 3 4 NULL NULL NULL NULL NULL NULL NULL NULL

10 2014-07-27 08:38:25.2907814 3 2 UPDATE 4 5 3 5 NULL NULL NULL NULL NULL NULL

16 2014-07-27 08:38:25.2947816 3 3 UPDATE 5 6 5 7 3 6 3 7 3 8

23 2014-07-27 08:38:25.3017820 3 4 DELETE 6 NULL 7 NULL 6 NULL 7 NULL 8 NULL

3 2014-07-27 08:38:25.2857811 4 1 UPDATE 4 5 NULL NULL NULL NULL NULL NULL NULL NULL

9 2014-07-27 08:38:25.2907814 4 2 UPDATE 5 6 4 6 NULL NULL NULL NULL NULL NULL

15 2014-07-27 08:38:25.2947816 4 3 UPDATE 6 7 6 8 4 7 4 8 4 9

22 2014-07-27 08:38:25.3017820 4 4 DELETE 7 NULL 8 NULL 7 NULL 8 NULL 9 NULL

2 2014-07-27 08:38:25.2857811 5 1 UPDATE 5 6 NULL NULL NULL NULL NULL NULL NULL NULL

8 2014-07-27 08:38:25.2907814 5 2 UPDATE 6 7 5 7 NULL NULL NULL NULL NULL NULL

14 2014-07-27 08:38:25.2947816 5 3 UPDATE 7 8 7 9 5 8 5 9 5 10

19 2014-07-27 08:38:25.2987819 5 4 DELETE 8 NULL 9 NULL 8 NULL 9 NULL 10 NULL

1 2014-07-27 08:38:25.2857811 6 1 UPDATE 6 7 NULL NULL NULL NULL NULL NULL NULL NULL

7 2014-07-27 08:38:25.2907814 6 2 UPDATE 7 8 6 8 NULL NULL NULL NULL NULL NULL

13 2014-07-27 08:38:25.2947816 6 3 UPDATE 8 9 8 10 6 9 6 10 6 11

21 2014-07-27 08:38:25.3017820 6 4 DELETE 9 NULL 10 NULL 9 NULL 10 NULL 11 NULL

Edit(added xml query)