Trigger UPDATE() and COLUMNS_UPDATED() functions

Question

I have AFTER UPDATE trigger on table.

I need to get the name of changing colomn and it's old and new values.

To do statement UPDATE(column_name) with each column in code - bad solution. But I can't get all table colomns names via query

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.Columns 
WHERE TABLE_NAME = 'smth'

and in cursor dynamically get UPDATE(@column_name) value.

At same time when I try to use COLUMNS_UPDATED() function when I update only one column in table, I have results (value converted to int):

64 (Updated column with ORDINAL_POSITION = 31)
32 (Updated column with ORDINAL_POSITION = 30)
8 (Updated column with ORDINAL_POSITION = 29)
4 (Updated column with ORDINAL_POSITION = 28)
2 (Updated column with ORDINAL_POSITION = 27)
1 (Updated column with ORDINAL_POSITION = 26)
32768 (Updated column with ORDINAL_POSITION = 25)

I think it is very strange and ask your help.

Answer 1

Whether it is strange or not, this is at least documented:

Caution

In SQL Server 2008, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when you query the INFORMATION_SCHEMA.COLUMNS view, as shown in the following example.

SELECT TABLE_NAME, COLUMN_NAME,
    COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
    COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM AdventureWorks2008R2.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact';