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