CREATE OR REPLACE TRIGGER TRI_KCSW_IDU AFTER INSERT OR DELETE OR UPDATE ON KCSW FOR EACH ROWdeclare
fcount float;
wlph varchar2(18);
kw varchar2(16);
ck_gcid varchar2(4);
ck_kjdwid varchar2(4);
ck_bmid varchar2(8);
ck_jhk varchar2(1);
ck_cwk varchar2(1);
ck_cwck varchar2(1);
ck_yxkc varchar2(1);
ck_cbjsfw varchar2(1);
jzrq date;
rkrq date;
jclx varchar2(4);
bcq number;
ck_jjff varchar2(2);
wlcbwlph varchar2(18);
begin
if DELETING or UPDATING then
wlph:=nvl(:old.KCSW_WLPH,' ');
kw:=nvl(:old.KCSW_KW,' ');
-- get CK info
select CK_GCID, CK_KJDWID, CK_JHK, CK_CWK, CK_SWCK, CK_YXKC, CK_CBJSFW, CK_BMID, CK_JJFF
into ck_gcid, ck_kjdwid, ck_jhk, ck_cwk, ck_cwck, ck_yxkc, ck_cbjsfw, ck_bmid, ck_jjff
from CK
where CK_CKID=:old.KCSW_CKID; if ck_jhk='Y' and :old.KCSW_SFCWZ<>'Y' then
/* WLJH */
update WLJH
set WLJH_KCSL=WLJH_KCSL-(:old.KCSW_RKSL-:old.KCSW_CKSL)
where WLJH_GCID=ck_gcid
and WLJH_WLID=:old.KCSW_WLID; if ck_yxkc='N' then
update WLJH
set WLJH_WXKCSL=WLJH_WXKCSL-(:old.KCSW_RKSL-:old.KCSW_CKSL)
where WLJH_GCID=ck_gcid
and WLJH_WLID=:old.KCSW_WLID;
end if;
end if; if ck_cwk='Y' then
if :old.KCSW_SFCWZ<>'S' then
/* WLCW */
update WLCW
set WLCW_KCSL=WLCW_KCSL-(:old.KCSW_CWRKSL-:old.KCSW_CWCKSL),
WLCW_SJKCJE=WLCW_SJKCJE-(:old.KCSW_SJRKJE-:old.KCSW_SJCKJE)
where WLCW_KJDWID=ck_kjdwid
and WLCW_WLID=:old.KCSW_WLID; if ABS(:old.KCSW_CWRKSL)>0.00001 and ABS(:old.KCSW_CWCKSL)<0.00001 then
update WLCW
set WLCW_SJCB=WLCW_SJKCJE/WLCW_KCSL
where WLCW_KJDWID=ck_kjdwid
and WLCW_WLID=:old.KCSW_WLID
and WLCW_SJKCJE>0.000001
and WLCW_KCSL>0.000001;
end if; /* WLCB */
if ck_jjff<>'15' then
wlcbwlph:=' ';
else
if wlph='' then
wlcbwlph:=' ';
else
wlcbwlph:=wlph;
end if;
end if; update WLCB
set WLCB_KCSL=WLCB_KCSL-(:old.KCSW_CWRKSL-:old.KCSW_CWCKSL),
WLCB_SJKCJE=WLCB_SJKCJE-(:old.KCSW_SJRKJE-:old.KCSW_SJCKJE)
where WLCB_KJDWID=ck_kjdwid
and WLCB_BMID=decode(ck_cbjsfw, 'B', ck_bmid, ' ')
and WLCB_CKID=decode(ck_cbjsfw, 'B', ' ', :old.KCSW_CKID)
and WLCB_WLID=:old.KCSW_WLID and WLCB_WLPH=wlcbwlph; if ABS(:old.KCSW_CWRKSL)>0.00001 and ABS(:old.KCSW_CWCKSL)<0.00001 then
update WLCB
set WLCB_SJCB=WLCB_SJKCJE/WLCB_KCSL
where WLCB_KJDWID=ck_kjdwid
and WLCB_BMID=decode(ck_cbjsfw, 'B', ck_bmid, ' ')
and WLCB_CKID=decode(ck_cbjsfw, 'B', ' ', :old.KCSW_CKID)
and WLCB_WLID=:old.KCSW_WLID and WLCB_WLPH=wlcbwlph
and WLCB_SJKCJE>0.000001
and WLCB_KCSL>0.000001; update WLCB
set WLCB_SJCB=WLCB_ZXJJ
where WLCB_KJDWID=ck_kjdwid
and WLCB_BMID=decode(ck_cbjsfw, 'B', ck_bmid, ' ')
and WLCB_CKID=decode(ck_cbjsfw, 'B', ' ', :old.KCSW_CKID)
and WLCB_WLID=:old.KCSW_WLID and WLCB_WLPH=wlcbwlph
and WLCB_SJKCJE+WLCB_KCSL<ABS(WLCB_SJKCJE)+ABS(WLCB_KCSL);
end if;
end if;
end if; if ck_cwck='Y' and :old.KCSW_SFCWZ<>'Y' then
/* KCHZ */
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw; if fcount>0 then
update KCHZ
set KCHZ_KCSL=KCHZ_KCSL-(:old.KCSW_RKSL-:old.KCSW_CKSL),
KCHZ_FZKCSL=KCHZ_FZKCSL-(:old.KCSW_FZRKSL-:old.KCSW_FZCKSL),
KCHZ_KCJE=KCHZ_KCJE-(:old.KCSW_SJRKJE-:old.KCSW_SJCKJE)
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
else
insert into KCHZ(KCHZ_CKID,KCHZ_WLID,KCHZ_WLPH,KCHZ_KW,KCHZ_KCSL,KCHZ_FZKCSL,KCHZ_KCJE,KCHZ_GYSID,KCHZ_KHID,KCHZ_CBDXID,KCHZ_PDRQ)
values(:old.KCSW_CKID,:old.KCSW_WLID,wlph,kw,:old.KCSW_CKSL-:old.KCSW_RKSL,:old.KCSW_FZCKSL-:old.KCSW_FZRKSL,
:old.KCSW_SJCKJE-:old.KCSW_SJRKJE,:old.KCSW_GYSID,:old.KCSW_KHID,:old.KCSW_CBDXID,sysdate);
end if; /* KCPH */
select count(*)
into fcount
from KCPH
where KCPH_WLID=:old.KCSW_WLID
and KCPH_WLPH=wlph;
if fcount>0 then
update KCPH
set KCPH_RKSL=KCPH_RKSL-:old.KCSW_RKSL,
KCPH_CKSL=KCPH_CKSL-:old.KCSW_CKSL
where KCPH_WLID=:old.KCSW_WLID
and KCPH_WLPH=wlph;
else
select WL_BCQ into bcq
from WL
where WL_WLID=:old.KCSW_WLID;
insert into KCPH(KCPH_WLID, KCPH_WLPH, KCPH_RKSL, KCPH_CKSL, KCPH_ZLH,
KCPH_FYCS, KCPH_FYRQ, KCPH_YFCS, KCPH_YFRQ, KCPH_SCRQ, KCPH_CJRQ, KCPH_SXRQ)
values(:old.KCSW_WLID,wlph,0-:old.KCSW_RKSL,0-:old.KCSW_CKSL,:old.KCSW_ZLH,
0,sysdate,0,sysdate,:old.KCSW_SCRQ,sysdate,:old.KCSW_SCRQ+bcq);
end if; /* KCPH */
end if; /* CK_SWCK='Y' */
/*KCHZ*/
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw
and KCHZ_RKRQ =:old.KCSW_RKRQ;
if fcount >0 then
select max(KCSW_RKRQ) into rkrq
from KCSW
where KCSW_CKID=:old.KCSW_CKID
and KCSW_WLID=:old.KCSW_WLID
and KCSW_WLPH=wlph
and KCSW_KW=kw;
update KCHZ
set KCHZ_RKRQ=rkrq
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
end if;
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw
and KCHZ_CKRQ =:old.KCSW_JZRQ;
if fcount >0 then
select max(KCSW_JZRQ) into jzrq
from KCSW
where KCSW_CKID=:old.KCSW_CKID
and KCSW_WLID=:old.KCSW_WLID
and KCSW_WLPH=wlph
and KCSW_KW=kw;
update KCHZ
set KCHZ_CKRQ=jzrq
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
end if;
end if; /* deleting or updating */
end TRI_KCSW_IDU;
fcount float;
wlph varchar2(18);
kw varchar2(16);
ck_gcid varchar2(4);
ck_kjdwid varchar2(4);
ck_bmid varchar2(8);
ck_jhk varchar2(1);
ck_cwk varchar2(1);
ck_cwck varchar2(1);
ck_yxkc varchar2(1);
ck_cbjsfw varchar2(1);
jzrq date;
rkrq date;
jclx varchar2(4);
bcq number;
ck_jjff varchar2(2);
wlcbwlph varchar2(18);
begin
if DELETING or UPDATING then
wlph:=nvl(:old.KCSW_WLPH,' ');
kw:=nvl(:old.KCSW_KW,' ');
-- get CK info
select CK_GCID, CK_KJDWID, CK_JHK, CK_CWK, CK_SWCK, CK_YXKC, CK_CBJSFW, CK_BMID, CK_JJFF
into ck_gcid, ck_kjdwid, ck_jhk, ck_cwk, ck_cwck, ck_yxkc, ck_cbjsfw, ck_bmid, ck_jjff
from CK
where CK_CKID=:old.KCSW_CKID; if ck_jhk='Y' and :old.KCSW_SFCWZ<>'Y' then
/* WLJH */
update WLJH
set WLJH_KCSL=WLJH_KCSL-(:old.KCSW_RKSL-:old.KCSW_CKSL)
where WLJH_GCID=ck_gcid
and WLJH_WLID=:old.KCSW_WLID; if ck_yxkc='N' then
update WLJH
set WLJH_WXKCSL=WLJH_WXKCSL-(:old.KCSW_RKSL-:old.KCSW_CKSL)
where WLJH_GCID=ck_gcid
and WLJH_WLID=:old.KCSW_WLID;
end if;
end if; if ck_cwk='Y' then
if :old.KCSW_SFCWZ<>'S' then
/* WLCW */
update WLCW
set WLCW_KCSL=WLCW_KCSL-(:old.KCSW_CWRKSL-:old.KCSW_CWCKSL),
WLCW_SJKCJE=WLCW_SJKCJE-(:old.KCSW_SJRKJE-:old.KCSW_SJCKJE)
where WLCW_KJDWID=ck_kjdwid
and WLCW_WLID=:old.KCSW_WLID; if ABS(:old.KCSW_CWRKSL)>0.00001 and ABS(:old.KCSW_CWCKSL)<0.00001 then
update WLCW
set WLCW_SJCB=WLCW_SJKCJE/WLCW_KCSL
where WLCW_KJDWID=ck_kjdwid
and WLCW_WLID=:old.KCSW_WLID
and WLCW_SJKCJE>0.000001
and WLCW_KCSL>0.000001;
end if; /* WLCB */
if ck_jjff<>'15' then
wlcbwlph:=' ';
else
if wlph='' then
wlcbwlph:=' ';
else
wlcbwlph:=wlph;
end if;
end if; update WLCB
set WLCB_KCSL=WLCB_KCSL-(:old.KCSW_CWRKSL-:old.KCSW_CWCKSL),
WLCB_SJKCJE=WLCB_SJKCJE-(:old.KCSW_SJRKJE-:old.KCSW_SJCKJE)
where WLCB_KJDWID=ck_kjdwid
and WLCB_BMID=decode(ck_cbjsfw, 'B', ck_bmid, ' ')
and WLCB_CKID=decode(ck_cbjsfw, 'B', ' ', :old.KCSW_CKID)
and WLCB_WLID=:old.KCSW_WLID and WLCB_WLPH=wlcbwlph; if ABS(:old.KCSW_CWRKSL)>0.00001 and ABS(:old.KCSW_CWCKSL)<0.00001 then
update WLCB
set WLCB_SJCB=WLCB_SJKCJE/WLCB_KCSL
where WLCB_KJDWID=ck_kjdwid
and WLCB_BMID=decode(ck_cbjsfw, 'B', ck_bmid, ' ')
and WLCB_CKID=decode(ck_cbjsfw, 'B', ' ', :old.KCSW_CKID)
and WLCB_WLID=:old.KCSW_WLID and WLCB_WLPH=wlcbwlph
and WLCB_SJKCJE>0.000001
and WLCB_KCSL>0.000001; update WLCB
set WLCB_SJCB=WLCB_ZXJJ
where WLCB_KJDWID=ck_kjdwid
and WLCB_BMID=decode(ck_cbjsfw, 'B', ck_bmid, ' ')
and WLCB_CKID=decode(ck_cbjsfw, 'B', ' ', :old.KCSW_CKID)
and WLCB_WLID=:old.KCSW_WLID and WLCB_WLPH=wlcbwlph
and WLCB_SJKCJE+WLCB_KCSL<ABS(WLCB_SJKCJE)+ABS(WLCB_KCSL);
end if;
end if;
end if; if ck_cwck='Y' and :old.KCSW_SFCWZ<>'Y' then
/* KCHZ */
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw; if fcount>0 then
update KCHZ
set KCHZ_KCSL=KCHZ_KCSL-(:old.KCSW_RKSL-:old.KCSW_CKSL),
KCHZ_FZKCSL=KCHZ_FZKCSL-(:old.KCSW_FZRKSL-:old.KCSW_FZCKSL),
KCHZ_KCJE=KCHZ_KCJE-(:old.KCSW_SJRKJE-:old.KCSW_SJCKJE)
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
else
insert into KCHZ(KCHZ_CKID,KCHZ_WLID,KCHZ_WLPH,KCHZ_KW,KCHZ_KCSL,KCHZ_FZKCSL,KCHZ_KCJE,KCHZ_GYSID,KCHZ_KHID,KCHZ_CBDXID,KCHZ_PDRQ)
values(:old.KCSW_CKID,:old.KCSW_WLID,wlph,kw,:old.KCSW_CKSL-:old.KCSW_RKSL,:old.KCSW_FZCKSL-:old.KCSW_FZRKSL,
:old.KCSW_SJCKJE-:old.KCSW_SJRKJE,:old.KCSW_GYSID,:old.KCSW_KHID,:old.KCSW_CBDXID,sysdate);
end if; /* KCPH */
select count(*)
into fcount
from KCPH
where KCPH_WLID=:old.KCSW_WLID
and KCPH_WLPH=wlph;
if fcount>0 then
update KCPH
set KCPH_RKSL=KCPH_RKSL-:old.KCSW_RKSL,
KCPH_CKSL=KCPH_CKSL-:old.KCSW_CKSL
where KCPH_WLID=:old.KCSW_WLID
and KCPH_WLPH=wlph;
else
select WL_BCQ into bcq
from WL
where WL_WLID=:old.KCSW_WLID;
insert into KCPH(KCPH_WLID, KCPH_WLPH, KCPH_RKSL, KCPH_CKSL, KCPH_ZLH,
KCPH_FYCS, KCPH_FYRQ, KCPH_YFCS, KCPH_YFRQ, KCPH_SCRQ, KCPH_CJRQ, KCPH_SXRQ)
values(:old.KCSW_WLID,wlph,0-:old.KCSW_RKSL,0-:old.KCSW_CKSL,:old.KCSW_ZLH,
0,sysdate,0,sysdate,:old.KCSW_SCRQ,sysdate,:old.KCSW_SCRQ+bcq);
end if; /* KCPH */
end if; /* CK_SWCK='Y' */
/*KCHZ*/
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw
and KCHZ_RKRQ =:old.KCSW_RKRQ;
if fcount >0 then
select max(KCSW_RKRQ) into rkrq
from KCSW
where KCSW_CKID=:old.KCSW_CKID
and KCSW_WLID=:old.KCSW_WLID
and KCSW_WLPH=wlph
and KCSW_KW=kw;
update KCHZ
set KCHZ_RKRQ=rkrq
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
end if;
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw
and KCHZ_CKRQ =:old.KCSW_JZRQ;
if fcount >0 then
select max(KCSW_JZRQ) into jzrq
from KCSW
where KCSW_CKID=:old.KCSW_CKID
and KCSW_WLID=:old.KCSW_WLID
and KCSW_WLPH=wlph
and KCSW_KW=kw;
update KCHZ
set KCHZ_CKRQ=jzrq
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
end if;
end if; /* deleting or updating */
end TRI_KCSW_IDU;
1) 删除库存记录时,若当前单据记录的入库日期(KCSW_RKRQ)为最大的入库日期【与KCHZ表的最后入库日期(KCHZ_RKRQ)相同】,则重新取库存事务记录的最大的入库日期KCSW_RKRQ作为最后入库日期KCHZ_RKRQ。
2) 删除库存记录时,若当前单据的出库日期(KC_JZRQ)为最大的出库日期【与KCHZ表的最后出库日期(KCHZ_CKRQ)相同】,则重新取库存记录的最大的入库日期KC_JZRQ作为最后出库日期KCHZ_CKRQ。
3) 重新取最大入出库日期时,需要根据仓库、物料、批号、库位去取。
为了实现上面的要求,我在原来的基础上加了这一段语句/*KCHZ*/
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw
and KCHZ_RKRQ =:old.KCSW_RKRQ;
if fcount >0 then
select max(KCSW_RKRQ) into rkrq
from KCSW
where KCSW_CKID=:old.KCSW_CKID
and KCSW_WLID=:old.KCSW_WLID
and KCSW_WLPH=wlph
and KCSW_KW=kw;
update KCHZ
set KCHZ_RKRQ=rkrq
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
end if;
select count(*)
into fcount
from KCHZ
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw
and KCHZ_CKRQ =:old.KCSW_JZRQ;
if fcount >0 then
select max(KCSW_JZRQ) into jzrq
from KCSW
where KCSW_CKID=:old.KCSW_CKID
and KCSW_WLID=:old.KCSW_WLID
and KCSW_WLPH=wlph
and KCSW_KW=kw;
update KCHZ
set KCHZ_CKRQ=jzrq
where KCHZ_CKID=:old.KCSW_CKID
and KCHZ_WLID=:old.KCSW_WLID
and KCHZ_WLPH=wlph
and KCHZ_KW=kw;
end if;
ORA-06512: 在 "HNHF.TRI_KCSW_IDU", line 163
ORA-04088: 触发器 'HNHF.TRI_KCSW_IDU' 执行过程中出错
报这个错,该如何解决啊?
遍历表KCSW中的记录。
即:表KCSW是mutating only for a row-level trigger. This means that we cannot
query it in a row-level trigger, but we can in a statement-level trigger 你可以查询下相关Mutating Table(变异表)上trigger的应用。
create or replace trigger DDD
after insert on tbl_tables
for each row
declare
counts float;
pragma autonomous_transaction;
begin
...
if xxx then
...
commit;
end if;
end DDD;