Determining What Has Changed

Inside a trigger, you can check which columns are being updated by a DML operation using the UPDATE() and COLUMNS_UPDATE() functions. The UPDATE() function returns TRUE or FALSE based on whether the value of a specified column is being set (regardless of whether it's actually changing). COLUMNS_UPDATED() returns a bitmap representing all the columns being set. Here's an example (Listing 8-1):

Listing 8-1. A trigger example that demonstrates UPDATE().
 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity, Type int, Onhand int ) GO CREATE TABLE ToyTypes (Type int identity, MinOnhand int ) GO INSERT ToyTypes (MinOnhand) VALUES (10) INSERT ToyTypes (MinOnhand) VALUES (20) INSERT ToyTypes (MinOnhand) VALUES (15) INSERT ...

Get Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.