Guru's Guide to SQL Server™ Stored Procedures, XML, and HTML, The

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