Question

SQL 触发器与事务

触发器，当下订单时，货物的数量相应减少
create trigger trgAfterOrder
on OrderDetail
for insert
as
begin
declare
@cGoodsID as char(6),
@iAmount as int

select @cGoodsID = GoodsID, @iAmount = Amount
from inserted

update Goods
set Amount = Amount - @iAmount
where ID = @cGoodsID
end
现在，我想添加个事务，如果 在触发器之前，货物数量已经是0了，那就事务回滚
OrderDetail 的 insert 的操作 也 事务回滚！
请问这个要怎么弄啊？

Answer 1

触发器里面 ROOLBACK 就可以了

CREATE TABLE Goods(
id INT,
Amount INT
);

CREATE TABLE OrderDetail(
ID INT,
GoodsID INT,
Amount INT
);

INSERT INTO Goods VALUES (1, 100);

EXEC sp_addmessage 60005, 16,
@msgtext = N'Not Goods to use.',
@lang = 'us_english'
go
EXEC sp_addmessage 60005, 16,
@msgtext = N'库存不足',
@lang = '简体中文'
go

create trigger trgAfterOrder
on OrderDetail
for insert
as
begin
declare
@cGoodsID as int,
@iAmount as int,
@nowCount as INT

select @cGoodsID = GoodsID, @iAmount = Amount
from inserted

SELECT @nowCount = Amount
FROM Goods
where ID = @cGoodsID;

IF @nowCount - @iAmount < 0
BEGIN
RAISERROR(60005, 16, 1);
ROLLBACK;
END
ELSE
BEGIN
update Goods
set Amount = Amount - @iAmount
where ID = @cGoodsID
END
end

----
1> select * from goods;
2> go
id Amount
----------- -----------
1 100

(1 行受影响)
1> select * from OrderDetail;
2> go
ID GoodsID Amount
----------- ----------- -----------

(0 行受影响)

1> INSERT INTO OrderDetail VALUES(1, 1, 90);
2> go

(1 行受影响)
1> INSERT INTO OrderDetail VALUES(1, 1, 20);
2> go
消息 60005，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，过程 trgAfterOrd
er，第 21 行
库存不足
消息 3609，级别 16，状态 1，服务器 HOME-BED592453C\SQLEXPRESS，第 1 行
事务在触发器中结束。批处理已中止。

1> select * from goods;
2> go
id Amount
----------- -----------
1 10

(1 行受影响)
1> select * from OrderDetail;
2> go
ID GoodsID Amount
----------- ----------- -----------
1 1 90

(1 行受影响)