本帖最后由 Simon.hu 于 2019-2-19 16:28 编辑
大家都应该看过官方的库存案列,其中有出入单据发生增删改时是如何对于库存数量进行更改变化的。案列中做了一个辅助表用来记录单据在更改前和删除前的原始数据,再通过数量的增减后得出新的库存,提交后更新数据库中的库存数量。大家可能会觉得功能虽实现了,但是应用起来比较复杂,不便于维护。当然也有人用视图的聚合来计算库存数量,得取最新库存,在这里不讨论谁的优劣,只是介绍另一种更新数量的方法,不一定要用在库存上,其它应用中也可能需要用到。 我归纳了下触发更改数量的这种方式类似于其它Excel平台的回写公式,在增删改时触发命令更改目标数量,也有Excel平始称为业务公式,把改删前数量当作了变量可以在公式中调用进行数量更新回写,这样前台方便调用不用再写触发器了。 在这里借花献佛希望通过这个案列能让官方能做成命令让不会SQL的人的方便使用。触发的优点:前台不用做数据的计算,把数据丢到服务器去计算了,速度会非常快。也可以思路扩展下用触发去解决存储过程的问题,不用官方给的存储过程调用代码。
1.实现理论,数据增删改后SQL会在数据库中自动产生如下临时表,通过这两张表与更改目标表的数量进行加减得出新的
deleted 增改删前数据临时表
inserted 增改删后数据临时表
2.库表设计
a.库存表(物料ID,存量)
b.出入库表_子表(物料ID,出入量)3.下面是SQL脚本
Create trigger 出入库触发_库存表_存量 --取名
on 出入库表_子表 --触发放在此表
for insert,update,delete --在增删改时执行
as
begin
--新增
if(exists(select 1 from inserted) and not exists(select 1 from deleted)) --判断当前是新增?
update 库存表
set 存量=isnull(存量,0)+isnull(BCH.出入量,0) --存量的计算方法:存量=存量+新增量
FROM (select 物料ID,sum(数量) as 数量 from inserted group by 物料ID) as BCH --给新增后记录取名保存后【BCH】
WHERE BCH.物料ID=库存表.物料ID
--更新
if(exists(select 1 from inserted) and exists(select 1 from deleted)) --判断当前是更改?
begin
update 库存表
set 存量=isnull(存量,0)-isnull(BCQ.出入量,0)+isnull(BCH.出入量,0) --存量的计算方法:存量=存量-更新前+更新后
FROM (select 物料ID,sum(出入量) as 出入量 from Deleted group by 物料ID) as BCQ --给更改前记录取名更新前【BCQ】
,(select 物料ID,sum(出数量) as 出入量 from inserted group by 物料ID) as BCH --给更改后记录取名更新后【BCH】
WHERE BCQ.物料ID=库存表.物料ID and BCH.物料ID=库存表.物料ID
END
--删除
if(not exists(select 1 from inserted) and exists(select 1 from deleted)) --判断当前是否为删除?
begin
update 库存表
set 存量=isnull(存量,0)-isnull(BCQ.出入量,0) --存量的计算方法:存量=存量-删除前
FROM (select 物料ID,sum(出入量) as 出入量 from Deleted group by 物料ID) as BCQ --给删除前记录取名更新后【BCQ】
WHERE BCQ.物料ID=库存表.物料ID
end
end
|