作者supisces (被~~~切~~~八~~~段)
看板Database
标题[SQL ] The transaction ended in the trigger.
时间Thu Mar 22 13:03:52 2012
我用 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)
1F：→ supisces:是不是前面有 'BEGIN TRANSACTION', 03/26 15:32
2F：→ supisces:後面才能用 'ROLLBACK TRANSACTION'? 03/26 15:33