alter trigger DBA.tr_klomain_u_billstate
on DBA.klomain for update
as
if update(billstate) begin --1
--关于库存
declare @whcode_i char(10),@whposcode_i char(8),@billstate_i integer,
@whcode_d char(10),@whposcode_d char(8),@billstate_d integer,
@invcode_i char(20),@batch_i char(10),@packcode_i char(2),@unitcode_i char(2),
@invcode_d char(20),@batch_d char(10),@packcode_d char(2),@unitcode_d char(2),
@packqty_i integer,@singleqty_i numeric(8,2),@totalqty_i numeric(12,2),
@packqty_d integer,@singleqty_d numeric(8,2),@totalqty_d numeric(12,2),
@totalqty_wh numeric(12,2),@myrow integer,
--
@id_i numeric(10),@billid_i char(15),@fptype_i char(20),
@sumprice_i numeric(15,2),
@sumprice_jy numeric(15,2),@billdate_i timestamp
--end
declare cur_dxf dynamic scroll cursor for select I.id,I.billid,I.billdate,
I.sumprice,
I.invcode,I.batch,I.packcode,I.unitcode,I.packqty,
I.singleqty,I.totalqty,I.whcode,I.billstate,
D.invcode,D.batch,D.packcode,D.unitcode,D.packqty,
D.singleqty,D.totalqty,D.whcode,D.billstate from
deleted as D,inserted as I where
I.id=D.id
open cur_dxf
fetch next cur_dxf into @id_i,@billid_i,@billdate_i,
@sumprice_i,
@invcode_i,@batch_i,@packcode_i,@unitcode_i,@packqty_i,
@singleqty_i,@totalqty_i,@whcode_i,@billstate_i,
@invcode_d,@batch_d,@packcode_d,@unitcode_d,@packqty_d,
@singleqty_d,@totalqty_d,@whcode_d,
@billstate_d
while @@sqlstatus=0
begin --2
--uncheck in
if @billstate_d=1 and @billstate_i=0
begin
--同步库存
select @totalqty_wh=totalqty from
wh_kc where
whcode=@whcode_d
and invcode=@invcode_d
and ifnull(batch,'',batch)=ifnull(@batch_d,'',@batch_d)
and unitcode=@unitcode_d
select @myrow=@@rowcount
if @myrow>1
raiserror 100002 '|库存中此货品的多条记录,会导致误操作,不能保存!|'
if @myrow=0
insert into wh_kc(whcode,invcode,batch,unitcode,totalqty) values(
@whcode_d,@invcode_d,@batch_d,@unitcode_d,@totalqty_d)
else
update wh_kc set
totalqty=totalqty+@totalqty_d where
whcode=@whcode_d
and invcode=@invcode_d
and ifnull(batch,'',batch)=ifnull(@batch_d,'',@batch_d)
and unitcode=@unitcode_d
end
--check out
if @billstate_d=0 and @billstate_i=1
begin
--同步库存
select @totalqty_wh=totalqty from
wh_kc where
whcode=@whcode_i
and invcode=@invcode_i
and ifnull(batch,'',batch)=ifnull(@batch_i,'',@batch_i)
and unitcode=@unitcode_i
select @myrow=@@rowcount
if @myrow=0
raiserror 100001 '|库存中没有此货品,不能保存!|'
if @myrow>1
raiserror 100002 '|库存中此货品的多条记录,会导致误操作,不能保存!|'
if @totalqty_wh<@totalqty_i
raiserror 100003 '|库存中没有足够的此货品,不能保存!|'
update wh_kc set
totalqty=totalqty-@totalqty_i where
whcode=@whcode_i
and invcode=@invcode_i
and ifnull(batch,'',batch)=ifnull(@batch_i,'',@batch_i)
and unitcode=@unitcode_i
end
--4
fetch next cur_dxf into @id_i,@billid_i,@billdate_i,
@sumprice_i,
@invcode_i,@batch_i,@packcode_i,@unitcode_i,@packqty_i,
@singleqty_i,@totalqty_i,@whcode_i,@billstate_i,
@invcode_d,@batch_d,@packcode_d,@unitcode_d,@packqty_d,
@singleqty_d,@totalqty_d,@whcode_d,
@billstate_d end --2
close cur_dxf
end --1