高手帮看下触发器！在线等......

来源：百度知道 编辑：UC知道 时间：2024/05/15 20:52:45
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)
WHERE tb_Stock.GoodsID = (SELECT INSERTED.GoodsID FROM INSERTED) AND tb_Stock.StockID = (SELECT INSERTED.StockID FROM INSERTED)
END
IF((SELECT INSERTED.ReGoodsSort FROM INSERTED) = 0 )
BEGIN
UPDATE tb_Stock SET StockNum=(SELECT tb_Stock.StockNum-INSERTED.ReGoodsNum FROM INSERTED)
WHERE tb_Stock.GoodsID = (SELECT INSERTED.GoodsID FROM INSERTED) AND tb_Stock.StockID = (SELECT INSERTED.StockID FROM INSERTED)
END

这个触发器检测没有问题，但是不执行，就是判断那出问题了，把判断去了，每一个就能用了，但是不能两个放在一起，
还有，就是当我ReGoodsSort=1执行触发器以后，还要在表sell中把Num减去INSERTED.ReGoodsNum的值，望高手再补充一段，万分感谢！在线急等

这个判断有问题
IF((SELECT INSERTED.ReGoodsSort FROM INSERTED) = 1 )
因为
SELECT INSERTED.ReGoodsSort FROM INSERTED得到的结果可能是多条，例如批量插入数据的情况

你在这里最好这样处理
原代码部分：
IF((SELECT INSERTED.ReGoodsSort FROM INSERTED) = 1 )
BEGIN
UPDATE tb_Stock SET StockNum=(SELECT tb_Stock.StockNum+INSERTED.ReGoodsNum FROM INSERTED)
WHERE tb_Stock.GoodsID = (SELECT INSERTED.GoodsID FROM INSERTED) AND tb_Stock.StockID = (SELECT INSERTED.StockID FROM INSERTED)
END
修改为：
UPDATE tb_Stock
SET StockNum=INSERTED.ReGoodsNum
FROM INSERTED
WHERE INSERTED.ReGoodsSort = 1
AND tb_Stock.GoodsID = INSERTED.GoodsID
AND tb_Stock.StockID = INSERTED.StockID

其它修改的类似

当ReGoodsSort=1执行触发器以后，还要在表sell中把Num减去INSERTED.ReGoodsNum的值
我不知道你的关联条件是怎么的，如果跟上面的一样话，可以这样写
UPDATE sell
SET Num = Num - INSERTED.ReGoodsNum
FROM INSERTED
WHERE INSERTED.ReGoodsSort = 1
AND sell.GoodsID = INSERTED.GoodsID<