Trigger Error Handling For ASP

最近遇到一個情況,在A資料表內有不同的trigger,其中有個trigger 1的作法是在update數量時要去同時update另一個B資料表的數量,
而這個A資料表的另一個trigger 2也會被執行,如果這時trigger 1內的另一個C資料內的Constraint裡觸發時,ASP的ADO會誤以為Trigger 2執行成功,
而不會返回錯誤,以致於程式裡無法作error handling

此時的解法就只能去調整trigger的執行順序,讓有Constraint的Trigger能先被執行,這樣才能抓到錯誤。
以下是範例示範:

-- 1. Constraint : CK_MyProduct_Qty_Selling
ALTER TABLE [dbo].[MyProduct]  WITH NOCHECK ADD  CONSTRAINT [CK_MyProduct_Qty_Selling]
CHECK  (([MyProductQty]-[MyProductQtySelling])>=(0) AND [MyProductQty]>(0))
GO
ALTER TABLE [dbo].[MyProduct] CHECK CONSTRAINT [CK_MyProduct_Qty_Selling]

-- 2. Trigger : InsertLog
CREATE TRIGGER  [dbo].[TRI_InsertLog]
 ON [dbo].[JesseWangDB]
 FOR UPDATE
AS 
 BEGIN 
     IF UPDATE (Product_Qty)
     BEGIN
         INSERT MyLog  .....
     END
 END
GO

-- 3. Trigger : UpdateDetails
CREATE TRIGGER  [dbo].[TRI_UpdateDetails]
 ON [dbo].[JesseWangDB]
 FOR UPDATE
AS
 DECLARE @OldProductId int

 BEGIN 
     IF UPDATE (Product_QtySelling)
     BEGIN
         SELECT @OldProductId=ProductId FROM deleted

         UPDATE     MyStock
         SET        MyStockQty = MyStockQty + 1
         WHERE MyStockProductId = @OldProductId
     END
 END
GO

當我執行以下程式時,觸發MyStock的Constraint :

UPDATE MyProduct SET MyProductQtySelling = MyProductQtySelling + 1 WHERE MyProductId = 38382

結果:


(1 個資料列受到影響)
訊息 547，層級 16，狀態 0，程序 TRI_UpdateDetails，行 29
UPDATE 陳述式與 CHECK 條件約束 "CK_MyProduct_Qty_Selling" 衝突。衝突發生在資料庫 "JesseWangDB"，資料表 "dbo.MyProduct"。
陳述式已經結束。


因為TRI_InsertLog先被執行到了,所以ASP的ADO誤以為執行結果無誤。

所以我們要調整Trigger順序

EXEC sp_settriggerorder @triggername=N'[dbo].[TRI_UpdateDetails]', @order=N'First', @stmttype=N'UPDATE'

調整後的結果:


訊息 547，層級 16，狀態 0，程序 TRI_UpdateDetails，行 29
UPDATE 陳述式與 CHECK 條件約束 "CK_MyProduct_Qty_Selling" 衝突。衝突發生在資料庫 "JesseWangDB"，資料表 "dbo.MyProduct"。
陳述式已經結束。


這樣ASP的ADO就可以抓到error了。