从表单价和数量引发主表的总价变化

最新推荐文章于 2026-01-04 16:20:20 发布

原创最新推荐文章于 2026-01-04 16:20:20 发布 · 445 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何使用SQL触发器在插入或更新采购订单时自动调整总价。针对不同的操作，触发器会采取相应的计算策略：插入时直接累加价格与数量的乘积；更新时则先将原有总价清零，再进行重新计算。

use CNData
go

Create Trigger AddPrice
ON dbo.Te_Purchasea
AFTER INSERT
AS
UPDATE dbo.Te_Purchase
SET Sum_Price = Sum_Price + inserted.Price * inserted.Req_Pur_Qty
from inserted,Te_Purchase
where inserted.Pur_No = Te_Purchase.Pur_No
go

Create Trigger UpdatePrice
ON dbo.Te_Purchasea
AFTER UPDATE
AS
if(UPDATE(Price) OR UPDATE(Req_Pur_Qty))
begin
  UPDATE dbo.Te_Purchase
  SET Sum_Price = 0
  from inserted
  where inserted.Pur_No = Te_Purchase.Pur_No
  
  UPDATE dbo.Te_Purchase
  SET Sum_Price = Sum_Price + Te_Purchasea.Price * Te_Purchasea.Req_Pur_Qty
  from inserted,Te_Purchasea
  where inserted.Pur_No = Te_Purchase.Pur_No
        and inserted.Pur_No = Te_Purchasea.Pur_No
end
go

简单思路 : 插入直接累加就好了如果是更新就要先清空再重新计算