2009/04/03

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了。

軟體工程的重要的指標