Best Practices

for RuBoard

Triggers are really just stored procedures in disguise. They have a few additional capabilities and nuances , but generally speaking, you take the same approach to writing a trigger as you would to build a stored procedure. A few additional thoughts:

• Make sure your triggers allow for the possibility that more than one row could be altered at once. Triggers that work fine with single-row operations often break when multirow operations come their way. Not allowing for multirow updates is the single most common error that trigger neophytes make.

• Begin each trigger by checking @@ROWCOUNT to see whether any rows have changed. If none have, exit immediately because there's nothing for the trigger to do.

• Use the UPDATE() and COLUMNS_UPDATED() functions to ensure the values you're wanting to verify have actually changed.

• Never wait for user input or any other user event within a trigger.

• Check for errors after significant operations within your triggers, especially DML operations. Commands within triggers should check for errors just as stored procedures should.

• Keep operations within a trigger to a minimum. Triggers should execute as quickly as possible to keep from adversely impacting system performance.

• Provide descriptive error messages without going overboard. Return user messages rather than obscure system error codes when possible.

• Modularize your triggers by locating code that's executed by multiple triggers or that's lengthy or complex in separate stored procedures.

• Check triggers that enforce referential integrity for robustness. Try every combination of columnar updates to be sure all scenarios are covered.

• Write a test script for every trigger you build. Make sure it tests every situation the trigger is supposed to handle.

Listing 8-16 presents a few more trigger examples:

Listing 8-16 General trigger examples.

 SET NOCOUNT ON USE pubs DROP TRIGGER SalesQty_INSERT_UPDATE GO CREATE TRIGGER SalesQty_INSERT_UPDATE ON sales AFTER INSERT, UPDATE AS IF @@ROWCOUNT=0 RETURN -- No rows affected, exit immediately IF (UPDATE(qty)) AND (SELECT MIN(qty) FROM inserted)<10 BEGIN   RAISERROR('Minimum order is 10 units',16,10)   ROLLBACK TRAN   RETURN END GO -- Test a single-row INSERT BEGIN TRAN   INSERT sales VALUES (6380,'ORD9997',GETDATE(),5,'Net 60','BU1032') IF @@TRANCOUNT>0 ROLLBACK TRAN GO -- Test a multirow INSERT BEGIN TRAN   INSERT sales   SELECT stor_id, ord_num+'A', ord_date, 5, payterms, title_id FROM sales IF @@TRANCOUNT>0 ROLLBACK TRAN GO DROP TRIGGER Sales_DELETE GO CREATE TRIGGER Sales_DELETE ON sales AFTER DELETE AS IF @@ROWCOUNT=0 RETURN -- No rows affected, exit immediately IF (@@ROWCOUNT>1) BEGIN   RAISERROR('Deletions of more than one row at a time are not permitted',16,10)   ROLLBACK TRAN   RETURN END GO BEGIN TRAN   DELETE sales IF @@TRANCOUNT>0 ROLLBACK TRAN GO DROP TRIGGER Salesord_date_qty_UPDATE GO CREATE TRIGGER Salesord_date_qty_UPDATE ON sales AFTER INSERT, UPDATE AS IF @@ROWCOUNT=0 RETURN -- No rows affected, exit immediately -- Check to see whether the 3rd and 4th columns are being updated simultaneously IF (COLUMNS_UPDATED() & (POWER(2,3-1)  POWER(2,4-1)))=12 BEGIN   UPDATE s SET payterms='Cash'   FROM sales s JOIN inserted i ON (s.stor_id=i.stor_id AND s.ord_num=i.ord_num)   IF (@@ERROR<>0) -- UPDATE generated an error, rollback transaction     ROLLBACK TRANSACTION   RETURN END GO -- Test with a single-row UPDATE BEGIN TRAN   UPDATE sales SET ord_date=GETDATE(), qty=15   WHERE stor_id=7066 and ord_num='A2976'   SELECT * FROM sales   WHERE stor_id=7066 and ord_num='A2976' IF @@TRANCOUNT>0 ROLLBACK TRAN GO -- Test with a multirow UPDATE BEGIN TRAN   UPDATE sales SET ord_date=GETDATE(), qty=15   WHERE stor_id=7066   SELECT * FROM sales   WHERE stor_id=7066 IF @@TRANCOUNT>0 ROLLBACK TRAN Server: Msg 50000, Level 16, State 10, Procedure CheckSalesQty, Line 3 Minimum order is 10 units Server: Msg 50000, Level 16, State 10, Procedure CheckSalesQty, Line 3 Minimum order is 10 units Server: Msg 50000, Level 16, State 10, Procedure CheckSalesDelete, Line 3 Deletions of more than one row at a time are not permitted stor_id ord_num              ord_date                    qty    payterms     title_id ------- -------------------- --------------------------- ------ ------------ -------- 7066    A2976                1999-06-13 01:10:16.193     15     Cash         PC8888 stor_id ord_num              ord_date                    qty    payterms     title_id ------- -------------------- --------------------------- ------ ------------ -------- 7066    A2976                1999-06-13 01:10:16.243     15     Cash         PC8888 7066    QA7442.3             1999-06-13 01:10:16.243     15     Cash         PS2091 

for RuBoard