create trigger tr
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
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
特别是type=1和join(......)T的T,我不理解啊
create table a(billNo varchar(10),whcode varchar(4),invcode varchar(50),qty numeric(14,2))
create table b(stockWh varchar(4),stockInv varchar(50),stockQty numeric(14,2))
--insert 触发器
drop trigger tr_out
go
create trigger tr_out
on a
after insert
as
declare @i cursor,
@sNo1 varchar(4),--(仓位)
@sSpno varchar(50),--(存货号)
@nDssl numeric(14,2),--(数量)
@nKcsl numeric(14,2)--(结存数量)
set @i=cursor for select whcode,invcode,qty from inserted
open @i
fetch next from @i into @sNo1,@sSpno,@nDssl
while(@@fetch_status=0)
begin
update b set stockQty=isnull(stockQty,0)-@nDssl where stockWh=@sNo1 and stockInv=@sSpno
fetch next from @i into @sNo1,@sSpno,@nDssl
end
close @I
deallocate @igo
--///////////删除触发器
drop trigger Dtr_out
go
create trigger tr_out
on a
after delete
as
declare @i cursor,
@sNo1 varchar(4),--(仓位)
@sSpno varchar(50),--(存货号)
@nDssl numeric(14,2),--(数量)
@nKcsl numeric(14,2)--(结存数量)
set @i=cursor for select whcode,invcode,qty from deleted
open @i
fetch next from @i into @sNo1,@sSpno,@nDssl
while(@@fetch_status=0)
begin
update b set stockQty=isnull(stockQty,0)+@nDssl where stockWh=@sNo1 and stockInv=@sSpno
fetch next from @i into @sNo1,@sSpno,@nDssl
end
close @I
deallocate @i
--测试
--先执行一下insert b
insert into b select '0001','1001',1000
--在执行一下 insert a
insert into a select '100001','0001','1001',100
insert into a select '100002','0001','1001',200
--此时 kcsl=700
--在执行一下delete
delete from a where billNo='100001'
--此时应该是 kcsl=800 这样总可以了吧!以后请多多指教!咱们是同行!
你能不能把我的字段和你的字段对应一下啊,spno是仓位吗?nol是存货号吗
你也是作进销存的,在那里作呢??我的QQ248200250(别忘了信息,进销存)
你的触发器,在出库为负数的情况下,当插入负数-2时,结存应为在原数据基础下加2才行,怎么是减2呢???插入负数-2相当冲销出库啊??
http://community.csdn.net/Expert/topic/4304/4304113.xml?temp=.3000147