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:
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';
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. ...