Microsoft SQL Server 2012 Bible

Working with the Transaction

SQL Server provides several ways for the trigger to determine the effects of the DML statement. The first two methods are the UPDATE() and columns_updated() functions, which are used to determine which columns were potentially affected by the DML statement. The other methods use deleted and inserted images, which contain the before and after data sets.

Determining the Updated Columns

SQL Server provides the UPDATE() function to test if a single column is affected by the DML transaction:

IF UPDATE(ColumnName)

Because an INSERT affects all columns, the UPDATE() function returns true for any column you pass. Conversely, UPDATE()always returns false for a DELETE. The following example demonstrates the UPDATE() function:

ALTER TRIGGER HumanResources.TriggerOne ON 
HumanResources.Department

AFTER INSERT, UPDATE
AS
IF Update(GroupName)
 BEGIN;
 PRINT ‘You might have modified the GroupName column';
 END;
ELSE
 BEGIN;
 PRINT ‘The GroupName column is untouched.';
 END;

With the trigger looking for changes to the LastName column, the following DML statement tests the trigger:

UPDATE 
HumanResources.Department

 SET GroupName = ‘Forecasting & Analytics
'
 WHERE 
Name = 
'Data Management';

Result:

You might have modified the GroupName column

Note that the UPDATE() function returns true even if the column is updated with the same value, for example, from ‘abc' to ‘abc'.

The columns_updated() function returns a bitmapped varbinary data type representation of the columns updated. ...