Get Columns Updated by Trigger

Applicability:

SQL Server 2000: N/A

SQL Server 2005: Not Tested

SQL Server 2008: Not Tested

SQL Server 2008R2: Not Tested

SQL Server 2012: Not Tested

SQL Server 2014: Not Tested

SQL Server 2016: Not Tested

SQL Server 2017: Not Tested

Credits:

Author: Binu Prasad M

Date: 27 Jun 2012

Description

Not able to test these in any environment at the moment, but I can see no reason why it wouldn't work

Code

--By BINU PRASAD.M, 2012/06/27



-- 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