[SQL ] The transaction ended in the trigger.
我用 MS SQL 2008 寫了一個 Trigger,
目的是檢查 new order 如果大於 in stock,
就取消 update.
Trigger 建立之後, 測試程式會出現:
The transaction ended in the trigger. The batch has been aborted.
---- SQL TRIGGER -------------------------
CREATE TRIGGER tr_check_qty
ON OrderDetails
FOR UPDATE
AS
DECLARE @stock smallint
DECLARE @neworder smallint
SELECT @stock = UnitsInStock
FROM Products
WHERE productid = (select productid from inserted)
SELECT @neworder = Quantity FROM inserted
IF @neworder > @stock
BEGIN
PRINT 'NO WAY JOSE'
ROLLBACK TRANSACTION
END
GO
---------- 測試程式 --------------
UPDATE OrderDetails --(The trigger should prevent the update.)
SET Quantity = 5
WHERE OrderID = 10008
AND ProductID = 21
----------- 測試結果 -----------
NO WAY JOSE
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
看起來 IF 的條件有成立, 只是不知道最後出現的 messege 是甚麼意思?
好像跟 ROLLBACK TRANSACTION 有關的樣子..
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 96.55.113.197
※ 編輯: supisces 來自: 96.55.113.197 (03/22 16:59)
→
03/26 15:32, , 1F
03/26 15:32, 1F
→
03/26 15:33, , 2F
03/26 15:33, 2F