O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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. ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required