帮看下简单的触发器

来源：百度知道 编辑：UC知道 时间：2024/06/10 16:17:37
CREATE TRIGGER t_ReGoods ON dbo.tb_ReGoods
FOR INSERT,UPDATE
AS
IF((SELECT INSERTED.ReGoodsSort FROM INSERTED) = 1 )
BEGIN
UPDATE tb_Stock SET StockNum=(SELECT tb_Stock.StockNum+INSERTED.ReGoodsNum FROM INSERTED)
UPDATE tb_Stock SET NeedPay=(SELECT tb_Stock.StockNum*tb_Stock.GoodsPrice FROM Inserted)
UPDATE tb_Stock SET HasPay=(SELECT tb_Stock.StockNum*tb_Stock.SellPrice FROM Inserted)
WHERE tb_Stock.GoodsID = (SELECT INSERTED.GoodsID FROM INSERTED)
UPDATE tb_Sell SET GoodsNum=(SELECT tb_sell.GoodsNum-INSERTED.ReGoodsNum FROM INSERTED)
UPDATE tb_Sell SET HasPay=(SELECT tb_Sell.GoodsNum*tb_Sell.SellPrice FROM Inserted)
UPDATE tb_Sell SET NeedPay=(SELECT tb_Sell.GoodsNum*tb_Sell.GoodsPrice FROM Inserted)
WHERE tb_Sell.GoodsID = (SELECT INSERTED.GoodsID FROM INSERTED)

END
IF((SELECT INSERTED.ReGoodsSort FROM INSERTED) = 0 )
BEGIN
UPDATE tb_Stock SET StockNum=(SELECT tb_Stock.StockNum-INSERTED.R

处理批量更新时，要用From语句进行Update，如下：

CREATE TRIGGER t_ReGoods ON dbo.tb_ReGoods
FOR INSERT,UPDATE
AS
--ReGoodsSort为1的更新操作：
UPDATE tb_Stock
SET StockNum=tb_Stock.StockNum+Inserted.GoodsNum
,NeedPay= (tb_Stock.StockNum+Inserted.GoodsNum) * tb_Stock.GoodsPrice
,HasPay = (tb_Stock.StockNum+Inserted.GoodsNum) * tb_Stock.SellPrice
FROM Inserted
WHERE tb_Stock.[GoodsID]=Inserted.[GoodsID]
AND INSERTED.ReGoodsSort = 1

UPDATE tb_Sell
SET GoodsNum=tb_sell.GoodsNum-INSERTED.ReGoodsNum
,HasPay=(tb_sell.GoodsNum-INSERTED.ReGoodsNum) * tb_Sell.SellPrice
,NeedPay=(tb_sell.GoodsNum-INSERTED.ReGoodsNum) * tb_Sell.GoodsPrice
WHERE tb_Sell.[GoodsID]=Inserted.[GoodsID]
AND INSERTED.ReGoodsSort = 1

--ReGoodsSort为0的更新操作：
UPDATE tb_Stock
SET StockNum=tb_Stock.StockNum-INSERTED.ReGoodsNum
,NeedPay=(tb_Stock.StockNum-INSERTED.ReGoodsNum) * tb_Stock.GoodsPrice
,HasPay=(tb_Stock.StockNu