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
执行有误,怎么插入了数据,在结存表中却减少呢?
能不能帮我分别写出插入触发器和删除触发器的代码啊
on a
after insert
as
declare @i cursor,
@sChdm varchar(7),--存货号
@sCw varchar(2),--仓位
@nQty numeric(14,2),
@nStockQty numeric(14,2)
set @i=cursor for select invcode,qty,whcode from a
open @i
fetch next from @i into @sChdm,@nQty,@sCw
while(@@fetch_status=0)
begin
update b set stockqty=stockqty-@nQty where stockinv=@sChdm and stockwh=@sCw
fetch next from @i into @sChdm,@nQty,@sCw
end
close @I
deallocate @i
这是个插入触发器
on a
after delete
as
declare @i cursor,
@sChdm varchar(7),--存货号
@sCw varchar(2),--仓位
@nQty numeric(14,2),
@nStockQty numeric(14,2)
set @i=cursor for select invcode,qty,whcode from a
open @i
fetch next from @i into @sChdm,@nQty,@sCw
while(@@fetch_status=0)
begin
update b set stockqty=stockqty+@nQty where stockinv=@sChdm and stockwh=@sCw
fetch next from @i into @sChdm,@nQty,@sCw
end
close @I
deallocate @i
这是个删除触发器
你的触发器不能正常运行呢?我删除表,B表没有更新呢
create table a(djno varchar(10),no1 varchar(4),spno varchar(50),dssl numeric(14,2))
create table b(no1 varchar(4),spno varchar(50),kcsl 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 no1,spno,dssl from inserted
open @i
fetch next from @i into @sNo1,@sSpno,@nDssl
while(@@fetch_status=0)
begin
print @sNo1
print @sSPno
print @nDssl
update b set kcsl=isnull(kcsl,0)-@nDssl where no1=@sNo1 and spno=@sSpno
fetch next from @i into @sNo1,@sSpno,@nDssl
end
close @I
deallocate @igo
--///////////删除触发器
drop trigger tr_out
go
create trigger tr_Dout
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 no1,spno,dssl from deleted
open @i
fetch next from @i into @sNo1,@sSpno,@nDssl
while(@@fetch_status=0)
begin
print @sNo1
print @sSPno
print @nDssl
update b set kcsl=isnull(kcsl,0)+@nDssl where no1=@sNo1 and spno=@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 djno='100001'
--此时应该是 kcsl=800
你把字段改了,我对应不上了,还有就是对于删除触发器这句
update b set kcsl=isnull(kcsl,0)+@nDssl where no1=@sNo1
应为相减啊????
http://community.csdn.net/Expert/topic/4303/4303946.xml?temp=.5689051