لطفا منتظر باشید ...
Best Practices
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
