2. trigger after insert를 통해 mms_recv_01에 값을 넣고
3. 넣은 결과를 mms_erpif의 resultcode, resultmessage에 update 하려합니다.
질문을 하면서도 안될것 같네요, insert중에 update를 하려 하다니... 트리거 수행중 트리거 또 발생되는건데..
간단한 방법이 없을까요?
/********************
mms_erpif:
drop table mms_erpif;
create table mms_erpif( seq varchar2(100), key01 varchar2(100), data01 varchar2(100), data02 varchar2(100), inf_time varchar2(100), inf_resultcode varchar2(100), inf_resultmessage varchar2(100));
drop table mms_receive_01;
create table mms_receive_01 ( key01 varchar2(100), data01 varchar2(100), data02 varchar2(100), inf_time varchar2(100)); ********************/CREATE OR REPLACE TRIGGER mms_erpif_tr -- AFTER INSERT ON mms_erpif FOR EACH ROWDECLARE -- lvv_resultcode VARCHAR2 (10) := ''; lvv_resultmessage VARCHAR2 (100) := ''; lvv_changed_time VARCHAR2 (20) := ''; lvv_exist_flag VARCHAR2 (10) := '';------BEGIN -- SELECT TO_CHAR (SYSDATE, 'YYYYMMDD HH24MISS') INTO lvv_changed_time FROM DUAL;
-- IF INSERTING THEN DBMS_APPLICATION_INFO.set_module ('mms_erpif_TR', 'mms_erpif_TR' || ' CREATE');
BEGIN BEGIN SELECT DECODE (COUNT (*), 0, 'N', 'Y') INTO lvv_exist_flag FROM mms_receive_01 WHERE key01 = :new.key01; EXCEPTION WHEN OTHERS THEN lvv_exist_flag := 'N'; END;
IF (lvv_exist_flag ='Y') THEN lvv_resultcode := 'F'; lvv_resultmessage := 'Can not create data: Error exist data'; ELSE INSERT INTO mms_receive_01 (key01, data01, data02, inf_time) VALUES (:new.key01, :new.data01, :new.data02, lvv_changed_time);
------------- -- mms_receive_01 테이블 summary하여 mms_receive_sum 에 값넣는 코드 -------------
lvv_resultcode := 'S'; lvv_resultmessage := 'Complete create data!'; END IF; -- EXCEPTION WHEN OTHERS THEN lvv_resultcode := 'F'; lvv_resultmessage := 'Can not create data:' || SUBSTR (SQLERRM, 1, 300); END; -- -- END IF;--
--==========================-- WRITE LOG--========================== BEGIN UPDATE mms_erpif SET inf_time = lvv_changed_time, inf_resultcode = lvv_resultcode, inf_resultmessage = lvv_resultmessage WHERE seq= :new.seq; END;--EXCEPTION WHEN OTHERS THEN lvv_resultmessage := 'Error trigger!' || SUBSTR (SQLERRM, 1, 300);