|
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML Authors: Henderson K. Published year: 2005 Pages: 60-62/223 |
| for RuBoard |
In this chapter you learned how basic error handling works in Transact -SQL. You learned how to handle different types of errors in your own code and how to work around some of the quirks in Transact-SQL's error management. You learned about the importance of correctly managing transactions, and you were introduced to some ways of dealing with serious errors while transactions are active.
| for RuBoard |
| for RuBoard |
I'd rather be a "Has Been" than a "Never Was" any day of the week.
H. W. Kenton
A trigger is a special type of stored procedure that executes when a specified DML operation (an INSERT, DELETE, or UPDATE, or any combination thereof) occurs. They're typically used to ensure that business rules are followed or to do other work when a data modification occurs. A trigger is the SQL equivalent of a 3GL callback function or a hooked interrupt vector.
You construct and attach triggers to tables using the CREATE TRIGGER command. When a table is dropped, so are its triggers.
Most of the details of stored procedure programming apply equally well to triggers. In fact, because you can call a stored procedure from a trigger, you can effectively do anything in a trigger that a stored procedure can do. One thing that triggers don't normally do is return result sets. Most front ends have no way of handling trigger-generated result sets, so you don't see this done very often. SQL Server doesn't permit triggers to return result codes.
Triggers fire once per statement, not per row, regardless of the number of rows changed by a given DML statement. You can set up triggers that fire after a DML operation (known as AFTER triggers, the default and most common type), and you can set up triggers that fire instead of a DML operation (INSTEAD OF triggers). We'll discuss each of these separately.
You can set up as many AFTER triggers as you want for a given table. Using sp_settriggerorder, you can designate a first and a last AFTER trigger on each table; the rest fire in no particular order. You're allowed just one INSTEAD OF trigger per DML operation on each table or view, although you can easily circumvent this by creating multiple views over the table or view in question and attaching INSTEAD OF triggers to each of them.
Declarative referential integrity (DRI) constraints have precedence over triggers. This means that a violation of a DRI constraint by a DML command will prevent triggers from executing.
| for RuBoard |
| for RuBoard |
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):
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():
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., 2 = 1, 2 1 = 2, and 2 2 = 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 (2 = 1, 2 1 = 2, 1 + 2 = 3). Here's a version of the trigger that checks for changes to multiple columns (Listing 8-3):
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 2 1 = 2 and 2 2 = 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:
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):
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.
| for RuBoard |
|
The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML Authors: Henderson K. Published year: 2005 Pages: 60-62/223 |