create table A ( billNo varchar(10), invcode varchar(10), qty int, whcode varchar(10) ) create table B ( stockInv varchar(10), stockQty int, stockWh varchar(10) )insert B select '0101001',500,'10' union select '0120222',500,'10' union select '0120011',500,'10' go--创建触发器 create trigger tra on A for insert as --更新B表,添加了出库单等于减少了结存 update B set stockQty=B.stockQty+T.qty from B join ( select invcode ,whcode ,sum(case when type=1 then -qty else qty end ) as 'qty' from ( select *,1 as 'type' from inserted union all select *,2 from deleted )tb group by invcode,whcode )T on B.stockInv=T.invcode and B.stockWh=T.whcode go--测试 insert A select 'A001','0101001',100,'10' union select 'A002','0120222',200,'10' union select 'A003','0120011',300,'10' --查看 select * from B/* stockInv stockQty stockWh ---------- ----------- ---------- 0101001 400 10 0120011 200 10 0120222 300 10(所影响的行数为 3 行) */ insert A select 'A001','0101001',-100,'10'--查看 select * from B/* stockInv stockQty stockWh ---------- ----------- ---------- 0101001 500 10 0120011 200 10 0120222 300 10(所影响的行数为 3 行) */--删除测试环境 drop table A,B
仁兄你也太依靠人了吧!人家就一句而已! create trigger tra on A for insert,delete as --更新B表,添加了出库单等于减少了结存 update B set stockQty=B.stockQty+T.qty from B join ( select invcode ,whcode ,sum(case when type=1 then -qty else qty end ) as 'qty' from ( select *,1 as 'type' from inserted union all select *,2 from deleted )tb group by invcode,whcode )T on B.stockInv=T.invcode and B.stockWh=T.whcode go
for insert,delete 没有 delete
这样就完美咯!^_^!我也是借花献佛咯 create trigger tra on A for insert,Update,delete as --更新B表,添加了出库单等于减少了结存 update B set stockQty=B.stockQty+T.qty from B join ( select invcode ,whcode ,sum(case when type=1 then -qty else qty end ) as 'qty' from ( select *,1 as 'type' from inserted union all select *,2 from deleted )tb group by invcode,whcode )T on B.stockInv=T.invcode and B.stockWh=T.whcode go无论你是插入,修改,删除都没问题咯
create trigger tra on A for insert,Update,delete as --更新B表,添加了出库单等于减少了结存 update B set stockQty=B.stockQty+T.qty from B join ( select invcode ,whcode ,sum(case when type=1 then -qty else qty end ) as 'qty' from ( select *,1 as 'type' from inserted union all select *,2 from deleted )tb group by invcode,whcode )T on B.stockInv=T.invcode and B.stockWh=T.whcode go无论你是插入,修改,删除都没问题咯
(
billNo varchar(10),
invcode varchar(10),
qty int,
whcode varchar(10)
)
create table B
(
stockInv varchar(10),
stockQty int,
stockWh varchar(10)
)insert B
select '0101001',500,'10' union
select '0120222',500,'10' union
select '0120011',500,'10'
go--创建触发器
create trigger tra
on A
for insert
as
--更新B表,添加了出库单等于减少了结存
update B
set stockQty=B.stockQty+T.qty
from B
join (
select invcode
,whcode
,sum(case when type=1 then -qty
else qty
end
) as 'qty'
from (
select *,1 as 'type' from inserted
union all
select *,2 from deleted
)tb
group by invcode,whcode
)T on B.stockInv=T.invcode
and B.stockWh=T.whcode
go--测试
insert A
select 'A001','0101001',100,'10' union
select 'A002','0120222',200,'10' union
select 'A003','0120011',300,'10' --查看
select * from B/*
stockInv stockQty stockWh
---------- ----------- ----------
0101001 400 10
0120011 200 10
0120222 300 10(所影响的行数为 3 行)
*/
insert A
select 'A001','0101001',-100,'10'--查看
select * from B/*
stockInv stockQty stockWh
---------- ----------- ----------
0101001 500 10
0120011 200 10
0120222 300 10(所影响的行数为 3 行)
*/--删除测试环境
drop table A,B
怎么会事,我删除一个负数(A表中),怎么结存表中是增加呢?按理,删除负数是取消冲入,结存减少才对啊??我是用表操作测试的!!!再帮我看看吧是什么原因
出库表delete记录,库存增加(如果为负数,实质是减少)
如果我们的理解反了,你在代码里改一下就可以了
这句+和-修也是不行的??
create trigger tra
on A
for insert,delete
as
--更新B表,添加了出库单等于减少了结存
update B
set stockQty=B.stockQty+T.qty
from B
join (
select invcode
,whcode
,sum(case when type=1 then -qty
else qty
end
) as 'qty'
from (
select *,1 as 'type' from inserted
union all
select *,2 from deleted
)tb
group by invcode,whcode
)T on B.stockInv=T.invcode
and B.stockWh=T.whcode
go
没有 delete
create trigger tra
on A
for insert,Update,delete
as
--更新B表,添加了出库单等于减少了结存
update B
set stockQty=B.stockQty+T.qty
from B
join (
select invcode
,whcode
,sum(case when type=1 then -qty
else qty
end
) as 'qty'
from (
select *,1 as 'type' from inserted
union all
select *,2 from deleted
)tb
group by invcode,whcode
)T on B.stockInv=T.invcode
and B.stockWh=T.whcode
go无论你是插入,修改,删除都没问题咯
on A
for insert,Update,delete
as
--更新B表,添加了出库单等于减少了结存
update B
set stockQty=B.stockQty+T.qty
from B
join (
select invcode
,whcode
,sum(case when type=1 then -qty
else qty
end
) as 'qty'
from (
select *,1 as 'type' from inserted
union all
select *,2 from deleted
)tb
group by invcode,whcode
)T on B.stockInv=T.invcode
and B.stockWh=T.whcode
go无论你是插入,修改,删除都没问题咯