Find List of Columns Updated inside Trigger

,

Use this in triggers only, mainly AFTER/INSTEAD OF UPDATE triggers and can be used to find the list of columns which were used in the UPDATE query in more generic way. This will be helpful to find the list for tables with columns more than 32.

-- Use this inside the trigger only. Change <<TABLE_NAME>> and <<SCHEMA_NAME>>
WITH t_result AS 

(

    SELECT    COLUMN_NAME, 

            CASE WHEN COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') % 8 = 0

                 THEN 7

                 ELSE (COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') % 8) -1

            END ColRelOrder, 

            SUBSTRING(COLUMNS_UPDATED(),

                        ((COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') - 1)/8) + 1, 1) ColModified

    FROM INFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = '<<TABLE_NAME>>' AND TABLE_SCHEMA = '<<SCHEMA_NAME>>'

)

SELECT COLUMN_NAME FROM t_result WHERE ColModified & POWER(2, ColRelOrder) > 0

Rate

4 (5)

Share

Share

Rate

4 (5)