update table2 set table2.num=table2.num+a.num from table2 join (select sum(num) as num,code from inserted where sh='sh' group by code) a on table2.code=a.code
假定整单审核(审核后的单据不能修改): if update(sh) if (select top 1 sh from deleted)='sh' --减运算,和上面相似 else --加运算
--处理的触发器 create trigger tr_update on table1 for update as if update(flag) --只有更新了审核标志,才会进行处理 begin --还原审核 update b set num=isnull(b.num,0)-isnull(a.num,0) from table2 b join( select code,num=sum(num) from( select code,num from inserted where flag<>'sh' union all select code,num from deleted where flag='sh' )a group by code )a on a.code=b.code --加上已经审核的数据 update b set num=isnull(b.num,0)+isnull(a.num,0) from table2 b join( select code,num=sum(num) from( select code,num from deleted where flag<>'sh' union all select code,num from inserted where flag='sh' )a group by code )a on a.code=b.code end
--处理的触发器 create trigger tr_update on table1 for update as if update(flag) --只有更新了审核标志,才会进行处理 begin set xact_abort on --加上事务处理,保证处理完全成功/失败 --还原审核 update b set num=isnull(b.num,0)-isnull(a.num,0) from table2 b join( select code,num=sum(num) from( select code,num from inserted where flag<>'sh' union all select code,num from deleted where flag='sh' )a group by code )a on a.code=b.code --加上已经审核的数据 update b set num=isnull(b.num,0)+isnull(a.num,0) from table2 b join( select code,num=sum(num) from( select code,num from deleted where flag<>'sh' union all select code,num from inserted where flag='sh' )a group by code )a on a.code=b.code end
zjcxc(邹建) 兄,又见你帮小弟,真的很感激啊。我的情况是这样的。假设库存都是空。新增一个入库单0001 内容如下 编码 名称 数量 a 香蕉 1 b 苹果 1这样审核之后 库存数量变成了编码 名称 数量 a 香蕉 1 b 苹果 1 也就是说我在入库明细表上写的触发器update table2 set table2.num=table2.num + inserted.num from table2,inserted where table2.code = inserted.code and inserted.sh ='sh' 是对的。 但是如果入库的内容是编码 名称 数量 a 香蕉 1 b 苹果 1 a 香蕉 1结果审核之后库存数居然是 a 香蕉 1 b 苹果 1 也就是如果同一入库单中存在相同的编码,则我的触发器有问题。 好像是整个触发器是一个事务, 请兄弟帮忙。谢谢
TO ghost_old(PB初级程序员),此人好象什么都不懂,什么都要问... 此物居心不良,请大家不要再告诉它`~~
if update(sh)
if (select top 1 sh from deleted)='sh'
--减运算,和上面相似
else
--加运算
create trigger tr_update on table1
for update
as
if update(flag) --只有更新了审核标志,才会进行处理
begin
--还原审核
update b set num=isnull(b.num,0)-isnull(a.num,0)
from table2 b join(
select code,num=sum(num)
from(
select code,num from inserted where flag<>'sh'
union all
select code,num from deleted where flag='sh'
)a group by code
)a on a.code=b.code --加上已经审核的数据
update b set num=isnull(b.num,0)+isnull(a.num,0)
from table2 b join(
select code,num=sum(num)
from(
select code,num from deleted where flag<>'sh'
union all
select code,num from inserted where flag='sh'
)a group by code
)a on a.code=b.code
end
create trigger tr_update on table1
for update
as
if update(flag) --只有更新了审核标志,才会进行处理
begin
set xact_abort on --加上事务处理,保证处理完全成功/失败 --还原审核
update b set num=isnull(b.num,0)-isnull(a.num,0)
from table2 b join(
select code,num=sum(num)
from(
select code,num from inserted where flag<>'sh'
union all
select code,num from deleted where flag='sh'
)a group by code
)a on a.code=b.code --加上已经审核的数据
update b set num=isnull(b.num,0)+isnull(a.num,0)
from table2 b join(
select code,num=sum(num)
from(
select code,num from deleted where flag<>'sh'
union all
select code,num from inserted where flag='sh'
)a group by code
)a on a.code=b.code
end
内容如下
编码 名称 数量
a 香蕉 1
b 苹果 1这样审核之后 库存数量变成了编码 名称 数量
a 香蕉 1
b 苹果 1
也就是说我在入库明细表上写的触发器update table2 set table2.num=table2.num + inserted.num from table2,inserted where table2.code = inserted.code and inserted.sh ='sh' 是对的。
但是如果入库的内容是编码 名称 数量
a 香蕉 1
b 苹果 1
a 香蕉 1结果审核之后库存数居然是
a 香蕉 1
b 苹果 1
也就是如果同一入库单中存在相同的编码,则我的触发器有问题。
好像是整个触发器是一个事务,
请兄弟帮忙。谢谢