-- /* 매일 1 시에 트리거가 disable 되 있는 것을 확인한후 enable 시킨다 . *.
declare
v_jobnum number;
begin
DBMS_JOB.SUBMIT(v_jobnum, 'SUB_TRIG_ENABLE_P02;', sysdate, 'trunc(sysdate) +1 + 1/24' );
end;
--/*-----------------------------------------------------------------------------
-- * 트리거를 확인해서 재실행
-- *-----------------------------------------------------------------------------
-- * Object Name :
-- * Object Type : PROCEDURE
-- * Parent Table : USER_TRIGGERS
-- * Create Date : 2005-10-26 LHH
-- *---------------------------------------------------------------------------*/
CREATE OR REPLACE PROCEDURE SUB_TRIG_ENABLE_P02 IS
V_TRIG_NAME USER_TRIGGERS.TRIGGER_NAME%TYPE ;
V_TRIG_STATUS USER_TRIGGERS.STATUS%TYPE ;
CNT NUMBER := 0 ;
TRIG_TOT_CNT NUMBER ;
CURSOR TRIG_CURSOR IS
SELECT TRIGGER_NAME, STATUS FROM USER_TRIGGERS ;
BEGIN
DBMS_OUTPUT.ENABLE ;
-- 트리거의 갯수를 센다.
SELECT COUNT(*) INTO TRIG_TOT_CNT FROM USER_TRIGGERS ;
DBMS_OUTPUT.PUT_LINE( TRIG_TOT_CNT ||' 개 트리거 작동중 !!!');
OPEN TRIG_CURSOR ;
LOOP
CNT := CNT + 1 ;
FETCH TRIG_CURSOR INTO V_TRIG_NAME , V_TRIG_STATUS ;
IF ( TRIM(V_TRIG_STATUS) = 'DISABLED' ) THEN
BEGIN
DBMS_OUTPUT.PUT_LINE( V_TRIG_NAME ||' ENABLED !!!');
EXECUTE IMMEDIATE 'ALTER TRIGGER '||V_TRIG_NAME || ' ENABLE ';
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001, V_TRIG_NAME || ' trigger enable during error !!!' );
END ;
END IF ;
EXIT WHEN CNT >= TRIG_TOT_CNT ;
END LOOP ;
CLOSE TRIG_CURSOR ;
END SUB_TRIG_ENABLE_P02;
/
|
|
|
