데이터베이스 질문과 답변
CREATE or replace-- added during DDL generation.
TRIGGER ERPACC.TIDU_KFA02OM0 after Insert or Update or delete ON ERPACC.KFA02OM0 for each row
declare
iRtnVal Integer;
sKfYear VarChar2(36);
sInsChgb VarChar2(9);
sKdSes VarChar2(12);
Function fun_control_remain
( arg_companycode varchar2,
Arg_YearMonth VarChar2,
Arg_KfYear VarChar2,
Arg_Kfcod1 VarChar2,
Arg_Kfcod2 Number,
Arg_KfChgb VarChar2,
Arg_KfAmtNew Number,
Arg_KfAmtOld Number,
Arg_KfDeAmt Number,
Arg_PKfAmt Number,
Arg_Flag VarChar2)
return number
as
sMonth VarChar2(2);
iCount Integer;
begin
sMonth := Substr(Arg_YearMonth,5,2);
begin
if Arg_Flag = 'INSERT' then
if Arg_KfChgb = 'A' then /*신규취득*/
insert into kfa04om0
( companycode, kfyear, kfcod1, kfcod2, kfamt, kfdeamt,
kfdr01,
kfdr02,
kfdr03,
kfdr04,
kfdr05,
kfdr06,
kfdr07,
kfdr08,
kfdr09,
kfdr10,
kfdr11,
kfdr12,
kfcr01, kfcr02, kfcr03, kfcr04, kfcr05, kfcr06,
kfcr07, kfcr08, kfcr09, kfcr10, kfcr11, kfcr12,
kfde01, kfde02, kfde03, kfde04, kfde05, kfde06,
kfde07, kfde08, kfde09, kfde10, kfde11, kfde12,
kfdedt,
kfjan01, kfjan02, kfjan03, kfjan04, kfjan05,
kfdn01, kfdn02, kfdn03, kfdn04, kfdn05, kfdn06,
kfdn07, kfdn08, kfdn09, kfdn10, kfdn11, kfdn12,
kdepval, kdiffval,pkfamt, bkfamt)
values
( arg_companycode, Arg_KfYear, Arg_KfCod1, Arg_KfCod2, 0, 0,
decode(sMonth,'01',Arg_KfAmtNew,0),
decode(sMonth,'02',Arg_KfAmtNew,0),
decode(sMonth,'03',Arg_KfAmtNew,0),
decode(sMonth,'04',Arg_KfAmtNew,0),
decode(sMonth,'05',Arg_KfAmtNew,0),
decode(sMonth,'06',Arg_KfAmtNew,0),
decode(sMonth,'07',Arg_KfAmtNew,0),
decode(sMonth,'08',Arg_KfAmtNew,0),
decode(sMonth,'09',Arg_KfAmtNew,0),
decode(sMonth,'10',Arg_KfAmtNew,0),
decode(sMonth,'11',Arg_KfAmtNew,0),
decode(sMonth,'12',Arg_KfAmtNew,0),
0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0) ;
else
insert into kfa04om0
( companycode, kfyear, kfcod1, kfcod2, kfamt, kfdeamt,
kfdr01, kfdr02, kfdr03, kfdr04, kfdr05, kfdr06,
kfdr07, kfdr08, kfdr09, kfdr10, kfdr11, kfdr12,
kfcr01, kfcr02, kfcr03, kfcr04, kfcr05, kfcr06,
kfcr07, kfcr08, kfcr09, kfcr10, kfcr11, kfcr12,
kfde01, kfde02, kfde03, kfde04, kfde05, kfde06,
kfde07, kfde08, kfde09, kfde10, kfde11, kfde12,
kfdedt,
kfjan01, kfjan02, kfjan03, kfjan04, kfjan05,
kfdn01, kfdn02, kfdn03, kfdn04, kfdn05, kfdn06,
kfdn07, kfdn08, kfdn09, kfdn10, kfdn11, kfdn12,
kdepval, kdiffval,pkfamt, bkfamt)
VALUES
( arg_companycode, Arg_KfYear,Arg_Kfcod1,Arg_Kfcod2,decode(Arg_PKfAmt,0,Arg_KfAmtNew,null,Arg_KfAmtNew,Arg_PKfAmt),Arg_KfDeAmt,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
null,
0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, Arg_PKfAmt, 0);
end if;
elsif Arg_Flag = 'DELETE' then
delete from kfa04om0
where companycode = arg_companycode and kfyear = Arg_KfYear and kfcod1 = Arg_KfCod1 and kfcod2 = Arg_Kfcod2;
elsif Arg_Flag = 'UPDATE' then
UPDATE kfa04om0
SET KFAMT = Arg_KfAmtNew
where companycode = arg_companycode and kfyear = Arg_KfYear and kfcod1 = Arg_KfCod1 and kfcod2 = Arg_Kfcod2;
end if;
exception
when others then
Return -1;
end;
Return 1;
exception
when others then
Return -1;
end;
begin
begin
select kfyear into sKfYear from kfa07om0;
if sKfYear is null then
sKfYear := '0';
end if;
exception
when no_data_found then
sKfYear := '0';
end;
if sKfYear <> '0' then
if inserting then
if :new.kfchgb = 'A' then
if substr(:new.kfaqdt,1,4) <> sKfYear then -
begin
select substr(dym00,1,4), d_ses into sKfYear, sKdSes From kfz08om0 Where dym01 <= substr(:new.kfaqdt,1,6) and dym12 >= substr(:new.kfaqdt,1,6) ;
if sKdSes is null then
sKdSes := '0';
end if;
if sKfYear is null then
sKfYear := '0000';
end if;
exception
when others then
sKdSes := '0';
sKfYear := '0000';
end;
end if;
if (sKdSes = '0' or sKfYear = '0000') then
raise_application_error(-20010, '회기 찾을 수 없슴' );
end if;
end if;
iRtnVal := fun_control_remain(:new.companycode, substr(:new.kfaqdt,1,6),sKfYear,:new.kfcod1,:new.kfcod2,:new.kfchgb,:new.kfamt,0,:new.kfdeamt,:new.pkfamt,'INSERT');
if iRtnVal = -1 then
raise_application_error(
-20010,
' 갱신 실패!'
);
end if;
elsif updating then
if :old.kfdeamt <> :new.kfdeamt or :old.kfamt <> :new.kfamt then
iRtnVal := fun_control_remain(:new.companycode, substr(:new.kfaqdt,1,6),sKfYear,:new.kfcod1,:new.kfcod2,:new.kfchgb,:new.kfamt,0,:new.kfdeamt,:new.pkfamt,'UPDATE');
--raise_application_error(-20010, :new.kfdeamt||' '||sKfYear||' '||:new.kfcod1||' '||:new.kfcod2);
if iRtnVal = -1 then
raise_application_error(
-20010,
'갱신 실패!'
);
end if;
end if;
end if;
if inserting then
delete from kfa03ot0
where companycode = :new.companycode and kfcod1 = :new.kfcod1 and kfcod2 = :new.kfcod2 and
kfacseq = 1 and kfchgb <= 'B';
if :new.kfchgb <> 'A' then
sInsChgb := 'B';
else
sInsChgb := 'A';
end if;
insert into kfa03ot0
(companycode, kfcod1, kfcod2, kfacdat, kfacseq, kfchgb, kfcust,
kfdamt, kfcamt, kfrde,
kfrde01, kfrde02, kfrde03, kfrde04, kfrde05, kfrde06,
kfrde07, kfrde08, kfrde09, kfrde10, kfrde11, kfrde12)
values
(:new.companycode, :new.kfcod1, :new.kfcod2, :new.kfaqdt, 1, sInsChgb, null,
:new.kfamt, 0, 0,
0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0);
delete from kfa06ot0
where companycode = :new.companycode and kfcod1 = :new.kfcod1 and kfcod2 = :new.kfcod2;
elsif updating then
if :old.kfaqdt <> :new.kfaqdt then
update kfa03ot0 set kfacdat = :new.kfaqdt
where companycode = :new.companycode and kfcod1 = :new.kfcod1 and kfcod2 = :new.kfcod2 and kfchgb = 'A' and rownum < 2;
end if;
elsif deleting then
delete from kfa04om0
where companycode = :old.companycode and kfcod1 = :old.kfcod1 and kfcod2 = :old.kfcod2 ;
delete from kfa03ot0
where companycode = :old.companycode and kfcod1 = :old.kfcod1 and kfcod2 = :old.kfcod2;
insert into kfa06ot0 (companycode, kfcod1, kfcod2) values (:old.companycode, :old.kfcod1, :old.kfcod2) ;
end if;
end if;
end;
kfa02om0 테이블에 트리거가 걸려 있고
kfa04om0 테이블에 조건에 따라 변경사항을 적용시키는 거네요.
변경사항을 적용시키는 로직 부분을 별도 함수로 정의해서 처리하네요.
감사합니다.~~^^
) 버튼을 클릭하여 작성 하시면 됩니다.