Cool Tips‎ > ‎Tables and Triggers‎ > ‎

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




ċ
Andy Hughes,
Jun 10, 2020, 6:27 AM
Comments