SQL 触发器与事务
发布网友
发布时间：2022-04-11 15:25
共2个回答
热心网友
时间：2022-04-11 16:54
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 行受影响)
