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(SELECTCOLUMN_NAME,CASEWHEN COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') % 8 = 0 THEN 7ELSE (COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') % 8) -1END ColRelOrder,SUBSTRING(COLUMNS_UPDATED(), ((COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID') - 1)/8) + 1, 1) ColModifiedFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_NAME = '<<TABLE_NAME>>' AND TABLE_SCHEMA = '<<SCHEMA_NAME>>')SELECT COLUMN_NAMEFROM t_resultWHERE ColModified & POWER(2, ColRelOrder) > 0