Calling Stored Procedures

for RuBoard

A good way of modularizing your trigger code and cutting down on redundancy is to place the code shared in common by multiple triggers into a stored procedure and call the stored procedure from the triggers. The problem with this is that the inserted and deleted logical tables aren't available from a stored procedure. However, remember that from the perspective of an AFTER trigger, the modifications that fired it appear to have already taken place. This is also true of any code the trigger calls, including stored procedures. So, stored procedures called by the trigger do indeed have access to the data in the inserted and deleted tables by way of the table itself. The trigger can pass them the information they need to locate the rows contained in the inserted or deleted tables using the underlying table. Here's some code to illustrate (Listing 8-13):

Listing 8-13 Moving trigger code to stored procedures makes it more modular.

 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 DROP PROC CheckRowcount GO CREATE PROC CheckRowcount @rcnt int AS        IF @rcnt > 1 BEGIN          RAISERROR('You may only change one item at a time. You are attempting          to change %d rows in a single operation.',16,10,@rcnt)          ROLLBACK          RETURN        END GO DROP PROC CheckOnhand GO CREATE PROC CheckOnhand @Type int, @Onhand int AS IF EXISTS (  SELECT * FROM ToyTypes WHERE Type=@Type AND MinOnhand>@OnHand  ) BEGIN   RAISERROR('You may not lower an item''s Onhand quantity below its Mininum Onhand quantity',16,10)   ROLLBACK   RETURN END GO CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE AS DECLARE @rcnt int SET @rcnt=@@ROWCOUNT IF @rcnt=0 RETURN EXEC CheckRowcount @rcnt IF (UPDATE(Onhand)) BEGIN   DECLARE @Type int, @Onhand int   SELECT @Type=Type, @Onhand=Onhand FROM inserted  EXEC CheckOnhand @Type, @Onhand  END GO UPDATE ToyInventory SET Onhand=49 WHERE Toy=4 -- Fails because of trigger 

(Results)

 Server: Msg 50000, Level 16, State 10, Procedure CheckOnhand, Line 5 You may not lower an item's Onhand quantity below its Mininum Onhand quantity 

In this example we call two stored procedures from the trigger: one to check the number of rows being affected to be sure that we don't allow more than one row to be changed at a time, and one to ensure that the Onhand column has a valid value. Because we restrict the trigger to single-row updates, all we have to do to let CheckOnhand know what rows are being changed and what changes are being made to them is to cache the critical info in variables and pass them to CheckOnhand as parameters. CheckOnhand then takes these parameters and looks up the minimum on-hand value for the specified type in the ToyTypes table. If the on-hand value being inserted into ToyInventory is less than the minimum allowed, CheckOnhand raises an error message and rolls back the transaction.

Because both of these routines are now stored procedures, we can call them from other triggers. Moreover, updating our business rules is easier when these business rules are centralized in a small number of stored procedures rather than scattered across a collection of triggers. For example, when we moved the row count-checking code to its own procedure, we enhanced it to report the number of modifications being attempted when there are too many. If an operation attempts to change more than one row at a time, we display an error message and report the number of attempted changes. If this code was not ensconced in a stored procedure, we might have had to change every trigger on the system to ensure consistency between them.

What if we wanted to allow multirow operations, yet still be able to modularize our code in stored procedures? It's not much more difficult. Here's the code (Listing 8-14):

Listing 8-14 You can access modified data from within stored procedures called by triggers.

 USE tempdb GO CREATE TABLE ToyInventory (Toy int identity PRIMARY KEY,  Type int,  Onhand int ) GO CREATE TABLE ToyTypes (Type int identity PRIMARY KEY,  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 DROP PROC CheckOnhand GO CREATE PROC CheckOnhand @MinToy int, @MaxToy int AS IF EXISTS (SELECT * FROM ToyTypes t JOIN ToyInventory i ON (t.Type=i.Type)  WHERE i   .   Toy BETWEEN @MinToy AND @MaxToy  AND MinOnhand>i.Onhand) BEGIN   RAISERROR('You may not lower an item''s Onhand quantity below its Mininum Onhand quantity',16,10)   ROLLBACK   RETURN END GO CREATE TRIGGER ToyInventory_UPDATE ON ToyInventory AFTER UPDATE AS IF @@ROWCOUNT=0 RETURN IF (UPDATE(Onhand)) BEGIN        DECLARE @MinToy int, @MaxToy int        SELECT @MinToy=MIN(Toy), @MaxToy=MAX(Toy)        FROM inserted  EXEC CheckOnhand @MinToy, @MaxToy  END GO UPDATE ToyInventory SET Onhand=Onhand-1 

(Results)

 Server: Msg 50000, Level 16, State 10, Procedure CheckOnhand, Line 7 You may not lower an item's Onhand quantity below its Mininum Onhand quantity 

Here we save the minimum and maximum values for the Toy column from the inserted logical table. All we need to establish is the range of values in ToyInventory that CheckOnhand should examine. Once we've done this, we pass this range into CheckOnhand via parameters.

CheckOnhand takes the parameters we've passed it and joins the ToyTypes and ToyInventory tables using an equi-join on the Type column and using a theta join on the Onhand column. If it finds rows in ToyInventory within the specified range of key values that have a lower on-hand quantity than they should, it raises an error message and rolls back the transaction. Thus, we have effectively moved the on-hand validation code from the trigger to a stored procedure it calls, and have given the procedure full access to the data currently being modified.

for RuBoard