안녕하세요. 오라클11G 이력 관리를 하려 합니다. 아래와 같이 코드를 작성하여 실행을 하면 문제 없이 설정이 됩니다. 하지만 패키지와 SP를 수정 하면 아래와 같은 오류가 발생 됩니다. ORA-04098 '' 트리거가 부적합하며 재검증을 실패 했습니다. --테이블 삭제 --DROP TABLE SOURCE_HISTORY --테이블 생성 CREATE TABLE SOURCE_HISTORY AS SELECT SYSDATE AS CHANGE_DATE , SI.SYSDATE2 , US.NAME AS NAME , US.TYPE , WMSYS.WM_CONCAT(US.TEXT) AS TEXT , 'N' AS COMMIT , SI.IP , SI.DBUSER , SI.PCNAME , SI.TERMINAL , SI.PC_USERNAME , SI.LOGIN_MODE FROM USER_SOURCE US, ( SELECT sys_context('USERENV', 'IP_ADDRESS') AS IP, sys_context('USERENV', 'SESSION_USER') AS DBUSER, sys_context('USERENV', 'HOST') AS PCNAME, sys_context('USERENV', 'TERMINAL') AS TERMINAL, sys_context('USERENV', 'OS_USER') AS PC_USERNAME, sys_context('USERENV', 'isDBA') AS LOGIN_MODE, TO_CHAR(SYSDATE, 'YYYYMMDDHHmmss') AS SYSDATE2 FROM DUAL )SI WHERE NAME = '패키지명' GROUP BY SYSDATE , SI.SYSDATE2 , US.NAME , US.TYPE , SI.IP , SI.DBUSER , SI.PCNAME , SI.TERMINAL , SI.PC_USERNAME , SI.LOGIN_MODE --drop trigger SOURCE_HISTORY_T --스키마 생성 CREATE OR REPLACE TRIGGER SOURCE_HISTORY_T AFTER CREATE ON APPS.SCHEMA DECLARE BEGIN IF ORA_DICT_OBJ_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'TYPE') THEN INSERT INTO SOURCE_HISTORY SELECT SYSDATE AS CHANGE_DATE , SI.SYSDATE2 , US.NAME AS NAME , US.TYPE , WMSYS.WM_CONCAT(US.TEXT) AS TEXT , 'N' AS COMMIT , SI.IP , SI.DBUSER , SI.PCNAME , SI.TERMINAL , SI.PC_USERNAME , SI.LOGIN_MODE FROM USER_SOURCE US, ( SELECT sys_context('USERENV', 'IP_ADDRESS') AS IP, sys_context('USERENV', 'SESSION_USER') AS DBUSER, sys_context('USERENV', 'HOST') AS PCNAME, sys_context('USERENV', 'TERMINAL') AS TERMINAL, sys_context('USERENV', 'OS_USER') AS PC_USERNAME, sys_context('USERENV', 'isDBA') AS LOGIN_MODE, TO_CHAR(SYSDATE, 'YYYYMMDDHHmmss') AS SYSDATE2 FROM DUAL )SI WHERE TYPE = ORA_DICT_OBJ_TYPE AND NAME = ORA_DICT_OBJ_NAME GROUP BY SYSDATE , SI.SYSDATE2 , US.NAME , US.TYPE , SI.IP , SI.DBUSER , SI.PCNAME , SI.TERMINAL , SI.PC_USERNAME , SI.LOGIN_MODE END IF; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-200000, SQLERRM); END;
고수님들의 조언 부탁 드립니다.