Determine which columns have been modified as a result of an UPDATE operation

we can Determine which columns have been modified as a result of an UPDATE operation using CLR Triggers, this is its one of unique capabilities.

Unique Capabilities of CLR Triggers

Triggers written in Transact-SQL have the capability of determining which columns from the firing view or table have been updated by using the UPDATE(column) and COLUMNS_UPDATED() functions.

Triggers written in a CLR language differ from other CLR integration objects in several significant ways. CLR triggers can:

*Reference data in the INSERTED and DELETED tables
*Determine which columns have been modified as a result of an UPDATE operation
*Access information about database objects affected by the execution of DDL statements.

These capabilities are provided inherently in the query language, or by the SqlTriggerContext class.


Determining Updated Columns

You can determine the number of columns that were modified by an UPDATE operation by using the ColumnCount property of the SqlTriggerContext object. You can use the IsUpdatedColumn method, which takes the column ordinal as an input parameter, to determine whether the column was updated. A True value indicates that the column has been updated.

For example, this code snippet (from the EmailAudit trigger later in this topic) lists all of the columns updated:

C#

reader = command.ExecuteReader();
reader.Read();
for (int columnNumber = 0; columnNumber < triggContext.ColumnCount; columnNumber++)
{
pipe.Send("Updated column "
+ reader.GetName(columnNumber) + "? "
+ triggContext.IsUpdatedColumn(columnNumber).ToString());
}

reader.Close();

Visual Basic


reader = command.ExecuteReader()
reader.Read()
Dim columnNumber As Integer

For columnNumber=0 To triggContext.ColumnCount-1

pipe.Send("Updated column " & reader.GetName(columnNumber) & _
"? " & triggContext.IsUpdatedColumn(columnNumber).ToString() )

Next

reader.Close()


Reference:http://msdn.microsoft.com/en-us/library/ms131093.aspx