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