Microsoft® SQL Server® 2012 Bible

887

Chapter 36: Creating Triggers

36

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

ment 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 representa-

tion of the columns updated. If the bit is true, then the column is updated. The result of

columns_updated() can be compared with integer or binary data by means of the bitwise

operators to determine whether a given column is updated.

The columns are represented by right-to-left bits within left-to-right bytes. A further

complication is that the size of the varbinary data returned by columns_updated()

depends on the number of columns in the table.

The following function simulates the actual behavior of the columns_updated() func-

tion. Passing the column to be tested and the total number of columns in the table returns

the column bitmask for that column:

c36.indd 887c36.indd 887 7/31/2012 10:03:08 AM7/31/2012 10:03:08 AM

http://www.it-ebooks.info