销售历史表:tb_sellHistory
当向此表插入数据如下:
goodsID goodsNumber
3 10
3 10
3 10
商品表tb_Shangpin goodsKucun,goodsSell做相应的修改
原表内容:
goodsID goodsKucun goodsSell
3 100 0
修改后欲让其变为:
3 70 30
我用触发器如下
CREATE TRIGGER t_Insert
ON tb_sellHistory
FOR INSERT
AS
UPDATE P SET
goodsKucun=P.goodsKucun-I.goodsNumber,
goodsSell=P.goodsSell+I.goodsNumber
FROM tb_Shangpin AS P INNER JOIN Inserted AS I
ON P.goodsID = I.goodsID
但是只能修改一个数据,即结果变为:
goodsID goodsKucun goodsSell
3 90 10
请高手给个实现上述的触发器!!谢谢
当向此表插入数据如下:
goodsID goodsNumber
3 10
3 10
3 10
商品表tb_Shangpin goodsKucun,goodsSell做相应的修改
原表内容:
goodsID goodsKucun goodsSell
3 100 0
修改后欲让其变为:
3 70 30
我用触发器如下
CREATE TRIGGER t_Insert
ON tb_sellHistory
FOR INSERT
AS
UPDATE P SET
goodsKucun=P.goodsKucun-I.goodsNumber,
goodsSell=P.goodsSell+I.goodsNumber
FROM tb_Shangpin AS P INNER JOIN Inserted AS I
ON P.goodsID = I.goodsID
但是只能修改一个数据,即结果变为:
goodsID goodsKucun goodsSell
3 90 10
请高手给个实现上述的触发器!!谢谢
sql 语句:
insert into tb_sellHistory select * from tb_sell
ON tb_sellHistory
for INSERT
AS
UPDATE P
SET goodsKucun=P.goodsKucun-I.goodsNumber,
goodsSell=P.goodsSell+I.goodsNumber
FROM
tb_Shangpin AS P
INNER JOIN
(select goodsID,sum(goodsNumber) goodsNumber from inserted) AS I ON P.goodsID = I.goodsID
ON tb_sellHistory
for INSERT
AS
UPDATE P
SET goodsKucun=P.goodsKucun-I.goodsNumber,
goodsSell=P.goodsSell+I.goodsNumber
FROM
tb_Shangpin AS P
INNER JOIN
(select goodsID,sum(goodsNumber) goodsNumber from inserted group by goodsID ) AS I ON P.goodsID = I.goodsID
CREATE TRIGGER t_Insert
ON tb_sellHistory
FOR INSERT
AS
begin
UPDATE tb_Shangpin
SET
goodsKucun = P.goodsKucun - I.goodsNumber,
goodsSell = P.goodsSell + I.goodsNumber
FROM tb_Shangpin AS P
INNER JOIN
(
select goodsID,sum(goodsNumber) as goodsNumber from Inserted group by goodsID
) AS I
ON P.goodsID = I.goodsID
end
(select goodsID,sum(goodsNumber) goodsNumber from inserted group by goodsID )
(select goodsID,sum(goodsNumber) as goodsNumber from Inserted group by goodsID)在麻烦大侠一下,这句是什么意思,只要给我简单的解释一下就行了,我只看懂个大概,呵呵呵
是一个有AS,一个没有AS吗?
把这一列sum(goodsNumber) 命名为goodsNumber 有无AS均可,有个空格就行句子功能一样
意思是 把插入的数据,按照每个goodsID进行分组求和sum(goodsNumber)当向此表插入数据如下:
goodsID goodsNumber
3 10
3 10
3 10
执行后变成
goodsID goodsNumber
3 30