لطفا منتظر باشید ...
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 ToyTypes (MinOnhand) VALUES (50)
INSERT ToyInventory (Type, Onhand) VALUES (1, 50)
INSERT ToyInventory (Type, Onhand) VALUES (2, 50)
INSERT ToyInventory (Type, Onhand) VALUES (3, 50)
INSERT ToyInventory (Type, Onhand) VALUES (4, 50)
GO
CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE
AS
DECLARE @rcnt int
SET @rcnt=@@ROWCOUNT
IF @rcnt=0 RETURN
IF @rcnt > 1 BEGIN
RAISERROR('You may only change one item at a time',16,10)
ROLLBACK
RETURN
END
IF (UPDATE(Onhand)) BEGIN
IF EXISTS (SELECT * FROM ToyTypes t JOIN inserted i ON t.Type=i.Type
WHERE t.MinOnhand>i.Onhand) BEGIN
RAISERROR('You may not lower an item''s Onhand quantity below its
Minimum Onhand quantity',16,10)
ROLLBACK
RETURN
END
END
GO
UPDATE ToyInventory SET Onhand=49 WHERE
Toy=4 -- Fails because of trigger
GO
DROP TABLE ToyInventory, ToyTypes
GO
This example not only shows how to use the UPDATE() function, it also shows how to code triggers so that they don't break when presented with multiple rows. In this case, we simply don't allow multiple row changes. We begin by checking @@ROWCOUNT to see if any rows have been changed at all. If none have, we return immediately.
Next, we check to see whether more than one row has been changed. If so, we display an error message, roll back the transaction, and exit. Notice that we store @@ROWCOUNT in a variable so that we can check it multiple times: once to see if any rows have been changed and once to see if too many have been changed.
If we get past both of these tests, we've got just one row to deal with. We begin validating the data modifications by seeing whether the Onhand column of the table has been changed. If it has, we check the MinOnhand column in the ToyTypes table to see whether the inventory is about to be reduced too far. If the current on-hand amount (as reported by the inserted table) is too low, we display an error message, roll back the transaction, and return. If not, we allow it to proceed.
We could just as easily have used the COLUMNS_UPDATED() function instead of UPDATE(). It has the advantage of being able to test for the modification of multiple columns at once. COLUMNS_UPDATED() returns a varbinary bitmap that indicates which columns have changed. Bits are ordered from left to right, with lower number columns being represented by the leftmost bits. Listing 8-2 presents the trigger rewritten to use COLUMNS_UPDATED():
Listing 8-2 A trigger example that demonstrates COLUMNS_UPDATED().
CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE
AS
DECLARE @rcnt int
SET @rcnt=@@ROWCOUNT
IF @rcnt=0 RETURN
IF @rcnt > 1 BEGIN
RAISERROR('You may only change one item at a time',16,10)
ROLLBACK
RETURN
END
IF ((COLUMNS_UPDATED() & 4)<>0) BEGIN
IF EXISTS (SELECT * FROM ToyTypes t JOIN inserted i ON t.Type=i.Type
WHERE t.MinOnhand>i.Onhand) BEGIN
RAISERROR('You may not lower an item''s Onhand quantity below its
Mininum Onhand quantity',16,10)
ROLLBACK
RETURN
END
END
Notice the binary AND operation (signified by the & operator) that's used to determine whether the Onhand column is being modified. Onhand is the third column in the table, so we use a value of 4 (bits are numbered starting with 0, so the third bit corresponds to 2 raised to the power of 2, or 4; i.e., 20 = 1, 21 = 2, and 22 = 4). If we wanted to test for multiple columns, we could easily do that. For example, we could test whether the first and second columns were being changed by testing COLUMNS_UPDATED() against the value of 3 because 3 has its 0 and 1 bits turned on (20 = 1, 21 = 2, 1 + 2 = 3). Here's a version of the trigger that checks for changes to multiple columns (Listing 8-3):
Listing 8-3 You can check multiple columns with COLUMNS_UPDATED().
CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE
AS
DECLARE @rcnt int
SET @rcnt=@@ROWCOUNT
IF @rcnt=0 RETURN
IF @rcnt > 1 BEGIN
RAISERROR('You may only change one item at a time',16,10)
ROLLBACK
RETURN
END
-- Test for changes to columns 2 and 3
IF ((COLUMNS_UPDATED() & 6)<>0) BEGIN --
Test for changes to columns 2 and 3
IF NOT EXISTS(SELECT * FROM ToyTypes t
JOIN inserted i ON t.Type=i.Type) BEGIN
RAISERROR('Invalid Toy Type',16,10)
ROLLBACK
RETURN
END
IF EXISTS (SELECT * FROM ToyTypes t
JOIN inserted i ON t.Type=i.Type
WHERE t.MinOnhand>i.Onhand) BEGIN
RAISERROR('You may not lower an item''s
Onhand quantity below its Mininum Onhand
quantity',16,10)
ROLLBACK
RETURN
END
END
Here we test against a value of 6 because we want to know whether the second and third columns (the Type and Onhand columns respectively) have been changed. Because 21 = 2 and 22 = 4, we OR these values together to return an integer with its second and third bits (bit numbers 1 and 2) enabled.
In INSERT triggers, COLUMNS_UPDATED() indicates that all columns are being set because all columns either receive explicit values or implicit ones via default constraints and nullability. An example is presented in Listing 8-4:
Listing 8-4 INSERT operations enable all bits in COLUMNS_UPDATED().
USE tempdb
GO
CREATE TABLE ToyInventory
(Toy int identity,
Type int NULL,
Onhand int DEFAULT 10
)
GO
CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT
AS
IF @@ROWCOUNT=0 RETURN
DECLARE @ChangedColumns varbinary(8000), @Size int, @i int
SET @ChangedColumns=COLUMNS_UPDATED()
SET @Size=DATALENGTH(@ChangedColumns)*8
SET @i=0
WHILE @i<@Size BEGIN
IF ((@ChangedColumns & POWER(2,@i))<>0)
PRINT 'Column '+CAST(@i AS varchar)+' changed'
SET @i=@i+1
END
GO
INSERT ToyInventory DEFAULT VALUES
GO
DROP TABLE ToyInventory
GO
(Results)
Column 0 changed
Column 1 changed
Column 2 changed
As you can see, even inserting DEFAULT VALUES into the table turns on the changed bit for all of its columns. Each of the three columns in ToyInventory has a different type of default value: Toy is an identity column, Type allows NULL values, and Onhand has a default constraint. As I said earlier, because each column in the table is required to receive a value of some type during an INSERT operation, COLUMNS_UPDATED() indicates that they've all been changed from within an INSERT trigger.
Note the loop we use to iterate through the changed column switches. It's worth discussing further. Here's the loop again:
WHILE @i<@Size BEGIN
IF ((@ChangedColumns & POWER(2,@i))<>0)
PRINT 'Column '+CAST(@i AS varchar)+' changed'
SET @i=@i+1
END
Because we previously saved the value of COLUMNS_UPDATED() in the @ChangedColumns variable, we can now loop through it, bit by bit, and determine which columns have changed. This technique would work in any trigger, not just INSERT triggers.
Note the use of POWER() to synthesize a binary value to check @ChangedColumns against. Because we're dealing with binary data and bit manipulation, we're obviously working with powers of 2. We begin by setting @Size to the actual number of bits returned by COLUMNS_UPDATED() (rounded up to the nearest byte), then we loop through these bits and check each one to see whether it has been set. For the ones that are enabled, we print a simple message.
What if we wanted to list the column names rather than just generic "Column n changed" messages? This wouldn't be terribly difficult. Here's the trigger modified to do just that (Listing 8-5):
Listing 8-5 It's easy to determine the names of the columns being changed.
USE tempdb
GO
CREATE TABLE ToyInventory
(Toy int identity,
Type int NULL,
Onhand int DEFAULT 10
)
GO
CREATE TRIGGER ToyInventory_INSERT ON ToyInventory AFTER INSERT
AS
IF @@ROWCOUNT=0 RETURN
DECLARE @ChangedColumns varbinary(8000),
@Size int, @i int, @colname sysname
SET @ChangedColumns=COLUMNS_UPDATED()
SET @Size=DATALENGTH(@ChangedColumns)*8
SET @i=0
WHILE @i<@Size BEGIN
IF ((@ChangedColumns & POWER(2,@i))<>0) BEGIN
SELECT @ColName=COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='ToyInventory' AND ORDINAL_POSITION-1=@i
PRINT 'Column '+@ColName+' changed'
END
SET @i=@i+1
END
GO
INSERT ToyInventory DEFAULT VALUES
(Results)
Column Toy changed
Column Type changed
Column Onhand changed
Note the use of the INFORMATION_SCHEMA.COLUMNS view to retrieve each column name using its ordinal index. This is preferable to querying the syscolumns table directly, although you could retrieve the same information from it.
