Oracle - Trigger Sample

[ Oracle - Trigger Sample ]

 

create or replace trigger lot_in_trg after insert or update or delete on lot_in for each row declare cnt number(4); seq_r number(4); qty number(11,3); qty_r number(11,3); errnum char(6):='-20999'; hhmm char(4); begin select to_char(sysdate,'hh24mi') into hhmm from dual; if inserting then if nvl(:new.qty_do,0) != 0 then /*------------------------------------------------------------------*/ /* (KEY부에 대한 변화는 delete후 insert가 됨, DATA부에 대한 변화는 */ /* update가 됨). */ /*------------------------------------------------------------------*/ update lot_stock set qty_norm = nvl(qty_norm,0) + nvl(:new.qty_do,0) where ymd = :new.ymd and lot = :new.lot and prdt_code = :new.prdt_code; if sql%notfound or sqlcode = 100 then insert into lot_stock (ymd, lot, prdt_code, qty_norm, qty_bad, bad_stat, bigo) values (:new.ymd, :new.lot, :new.prdt_code, :new.qty_do, 0, null , null ); end if; /*------------------------------------------------------------------*/ /* 생산량실적정보를 update한다. */ /*------------------------------------------------------------------*/ errnum := '-20901'; select count(*) into cnt from numbering where gubun = 'J' and ymd = :new.ymd; if cnt = 0 then seq_r := 1; errnum := '-20902'; insert into numbering (gubun, ymd, seq) values ('J', :new.ymd, seq_r); else select seq into seq_r from numbering where gubun = 'J' and ymd = :new.ymd; seq_r := seq_r + 1; errnum := '-20903'; update numbering set seq = seq_r where gubun = 'J' and ymd = :new.ymd; end if; errnum := '-20904'; insert into result (ymd, manage_no, line, prdt_code, day_gubun, qty, gubun, hhmm) values (:new.ymd, substr(:new.ymd,3,6) || lpad(to_char(seq_r),4,'0'), :new.line, :new.prdt_code, :new.day_gubun,:new.qty_do, 'PF', hhmm); end if; elsif updating then qty := nvl(:new.qty_do,0) - nvl(:old.qty_do,0); qty_r := nvl(:new.qty_do,0) - nvl(:old.qty_do,0); /*------------------------------------------------------------------*/ errnum := '-20991'; update lot_stock set qty_norm = nvl(qty_norm,0) + qty where ymd = :new.ymd and lot = :new.lot and prdt_code = :new.prdt_code; if sql%notfound or sqlcode = 100 then insert into lot_stock (ymd, lot, prdt_code, qty_norm, qty_bad, bad_stat, bigo) values (:new.ymd, :new.lot, :new.prdt_code, qty, 0, null , null); end if; /*------------------------------------------------------------------*/ /* 생산량실적정보를 update한다. */ /*------------------------------------------------------------------*/ errnum := '-20994'; if qty <> 0 then errnum := '-20901'; select count(*) into cnt from numbering where gubun = 'J' and ymd = :new.ymd; if cnt = 0 then seq_r := 1; errnum := '-20902'; insert into numbering (gubun, ymd, seq) values ('J', :new.ymd, seq_r); else select seq into seq_r from numbering where gubun = 'J' and ymd = :new.ymd; seq_r := seq_r + 1; errnum := '-20903'; update numbering set seq = seq_r where gubun = 'J' and ymd = :new.ymd; end if; errnum := '-20904'; insert into result (ymd, manage_no, line, prdt_code, day_gubun, qty, gubun, hhmm) values (:new.ymd, substr(:new.ymd,3,6) || lpad(to_char(seq_r),4,'0'), :new.line, :new.prdt_code, :new.day_gubun,qty_r, 'PF', hhmm); end if; elsif deleting then /*------------------------------------------------------------------*/ /* 삭제시에는 OLD.LOT, OLD.단량에서 OLD.수량만큼 -Update한다. */ /*------------------------------------------------------------------*/ errnum := '-20907'; update lot_stock set qty_norm = nvl(qty_norm,0) - nvl(:old.qty_do,0) where ymd = :old.ymd and lot = :old.lot and prdt_code = :old.prdt_code; if sqlcode = 100 or sql%notfound then null; end if; errnum := '-20909'; /*------------------------------------------------------------------*/ /* 생산량실적정보를 update한다. */ /*------------------------------------------------------------------*/ errnum := '-20907'; select count(*) into cnt from numbering where gubun = 'J' and ymd = :old.ymd; if cnt = 0 then seq_r := 1; errnum := '-20902'; insert into numbering (gubun, ymd, seq) values ('J', :old.ymd, seq_r); else select seq into seq_r from numbering where gubun = 'J' and ymd = :old.ymd; seq_r := seq_r + 1; errnum := '-20903'; update numbering set seq = seq_r where gubun = 'J' and ymd = :old.ymd; end if; errnum := '-20904'; insert into result (ymd, manage_no, line, prdt_code, day_gubun, qty, gubun, hhmm) values (:old.ymd, substr(:old.ymd,3,6) || lpad(to_char(seq_r),4,'0'), :old.line, :old.prdt_code, :old.day_gubun,:old.qty_do * -1, 'PF', hhmm); end if; /*-------------------------------------------------------*/ /*- 후처리 -----------------------------------------------*/ /*-------------------------------------------------------*/ delete from lot_stock where nvl(qty_norm,0) = 0 and nvl(qty_bad,0) = 0; if sqlcode = 100 or sql%notfound then null; end if; update lot_stock set seqno1 = null, seqno2 = null where nvl(qty_norm,0) < 0; if sqlcode = 100 or sql%notfound then null; end if; /*------------------------------------------------------------*/ /*- Exception처리 --------------------------------------------*/ /*------------------------------------------------------------*/ exception when no_data_found then raise_application_error(errnum,'Location재고 NOTFOUND! '); when others then raise_application_error(errnum,'Location재고보정처리중 Error발생!'); end; / 발주처 : http://myhome.netsgo.com/mino70/main_03_oracle03.htm