DDL Trigger 将操作记录起来,并修改相应表:CREATE OR REPLACE TRIGGER TRG_INTERFACE_BASE AFTER CREATE OR ALTER OR DROP OR TRUNCATE ON DATABASE
DECLARE oper i_ddl_log.operation%TYPE; txt_value varchar2(1800); num_count number; CURSOR C1 IS SELECT ora_sysevent T_EVT, ora_dict_obj_owner T_OWNER, ora_dict_obj_name T_NAME, upper(sql_text) T_TXT, USER T_USER, SYSDATE T_DATE FROM gv$open_cursor; BEGIN SELECT ora_sysevent INTO oper FROM dual;
FOR LP IN C1 LOOP select count(*) into num_count from I_TBLS WHERE TBL_NAME=lp.t_name AND FLG_CHECK='1'; IF NUM_COUNT>0 THEN IF LP.T_TXT LIKE LP.T_EVT || ' TABLE %' THEN IF OPER IN ('CREATE','DROP','TRUNCATE','ALTER') THEN INSERT INTO I_DDL_LOG ( OPERATION , OBJ_OWNER , OBJECT_NAME , SQL_TEXT , ATTEMPT_BY , ATTEMPT_DT , FLG_EXEC ) values( LP.T_EVT, LP.T_OWNER, LP.T_NAME, LP.T_TXT, LP.T_USER, LP.T_DATE, '0' ); select lp.t_txt into txt_value from dual; txt_value := replace(txt_value,lp.t_name,'I_' || lp.t_name); execute immediate txt_value; END IF; END IF; END IF;
END LOOP;/* EXCEPTION WHEN OTHERS THEN raise_application_error (num => -20000, msg => 'TRG_INTERFACE_BASE 未知错误,需人为干预.');*/
像DML中的:old ,:new
AFTER CREATE OR ALTER OR DROP OR TRUNCATE ON DATABASE
DECLARE
oper i_ddl_log.operation%TYPE;
txt_value varchar2(1800);
num_count number;
CURSOR C1 IS
SELECT ora_sysevent T_EVT, ora_dict_obj_owner T_OWNER,
ora_dict_obj_name T_NAME, upper(sql_text) T_TXT, USER T_USER, SYSDATE T_DATE
FROM gv$open_cursor;
BEGIN
SELECT ora_sysevent
INTO oper
FROM dual;
FOR LP IN C1 LOOP
select count(*) into num_count from I_TBLS WHERE TBL_NAME=lp.t_name AND FLG_CHECK='1';
IF NUM_COUNT>0 THEN
IF LP.T_TXT LIKE LP.T_EVT || ' TABLE %' THEN
IF OPER IN ('CREATE','DROP','TRUNCATE','ALTER') THEN
INSERT INTO I_DDL_LOG (
OPERATION ,
OBJ_OWNER ,
OBJECT_NAME ,
SQL_TEXT ,
ATTEMPT_BY ,
ATTEMPT_DT ,
FLG_EXEC
) values(
LP.T_EVT,
LP.T_OWNER,
LP.T_NAME,
LP.T_TXT,
LP.T_USER,
LP.T_DATE,
'0'
);
select lp.t_txt into txt_value from dual;
txt_value := replace(txt_value,lp.t_name,'I_' || lp.t_name);
execute immediate txt_value;
END IF;
END IF;
END IF;
END LOOP;/* EXCEPTION
WHEN OTHERS THEN
raise_application_error (num => -20000,
msg => 'TRG_INTERFACE_BASE 未知错误,需人为干预.');*/
END;