- Oracle 10g, 11g
Для начала создадим таблицу, куда триггер будет регистрировать события CREATE, ALTER, DROP.
CREATE TABLE TESTER.DDL_LOG (DDL_ID INTEGER, VER_ID INTEGER, ATTEMPT_DT DATE, OPERATION VARCHAR2(30 BYTE), COMP VARCHAR2(128 BYTE), MODULE VARCHAR2(255 BYTE), OS_USER VARCHAR2(255 BYTE), DB_USER VARCHAR2(30 BYTE), OBJ_OWNER VARCHAR2(30 BYTE), OBJECT_NAME VARCHAR2(30 BYTE), OBJECT_TYPE VARCHAR2(30 BYTE), SQL_TEXT CLOB, SQL_LENGTH NUMBER);
, где ddl_id — порядковый номер строк,
ver_id — порядковый номер версии по группе [obj_owner, object_name, object_type],
attempt_dt — дата изменения объекта,
operation — операция (CREATE|ALTER|DROP),
comp — хост, с которого выполнялось событие,
module — программный модуль, в котором выполнялось событие,
os_user — ОС пользователь,
db_user — БД пользователь,
obj_owner — схема, к которой относится объект,
object_name — имя объекта,
object_type — тип объекта,
sql_text — тело объекта (программный код),
sql_length — длина кода в символах
Создадим еще последовательность для генерации порядкового номера строк таблицы.
CREATE SEQUENCE TESTER.ddl_log_seq START WITH 0 increment by 1 MAXVALUE 999999999 MINVALUE 0 NOCYCLE NOCACHE ORDER;
Теперь создадим DDL-триггер, который будет срабатывать после событий CREATE, ALTER, DROP, и повесим его на базу (создавать под учеткой as sysdba).
CREATE OR REPLACE TRIGGER DDL_TRIGGER AFTER CREATE OR ALTER OR DROP ON DATABASE DECLARE vddl_id NUMBER; vver_id NUMBER; temp_clob CLOB; sql_clob CLOB; vsql_text ora_name_list_t; n NUMBER; text RAW(1000); text_blob BLOB; convert_warn INTEGER; dest_offset INTEGER := 1; src_offset INTEGER := 1; lang_context INTEGER := DBMS_LOB.default_lang_ctx; csid INTEGER := DBMS_LOB.default_csid; BEGIN -- Если нужно, то указываем, для каких схем и типов объектов будет срабатывать триггер после событий CREATE, ALTER, DROP IF ora_dict_obj_owner IN ('BIB', 'BIPS', 'BANKS', 'MTB', 'RAILWAY', 'STUSER') AND ora_dict_obj_type IN ('TABLE', 'PROCEDURE', 'FUNCTION', 'PACKAGE') THEN -- Формируем номер для новой строки SELECT TESTER.ddl_log_seq.nextval INTO vddl_id FROM dual; -- Формируем последовательный номер версии для группы [obj_owner,object_name,object_type] SELECT CASE WHEN EXISTs(SELECT count(1) vver_id FROM TESTER.ddl_log WHERE obj_owner = ora_dict_obj_owner AND object_name = ora_dict_obj_name AND object_type = ora_dict_obj_type GROUP BY obj_owner,object_name,object_type) THEN (SELECT max(ver_id)+1 vver_id FROM TESTER.ddl_log WHERE obj_owner = ora_dict_obj_owner AND object_name = ora_dict_obj_name AND object_type = ora_dict_obj_type GROUP BY obj_owner,object_name,object_type) ELSE 1 END INTO vver_id FROM dual; -- Получаем программный код объекта в CLOB после DDL-события (построчное склеивание для кодировки AL32UTF8 и не только) n := ora_sql_txt(vsql_text); DBMS_LOB.CREATETEMPORARY(text_blob,true); DBMS_LOB.open(text_blob,dbms_lob.lob_readwrite); FOR i IN 1 .. n LOOP text:=UTL_RAW.CAST_TO_RAW(vsql_text(i)); DBMS_LOB.writeappend(text_blob,utl_raw.length(text),text); END LOOP; DBMS_LOB.createtemporary(sql_clob, true); DBMS_LOB.converttoclob (sql_clob, text_blob, DBMS_LOB.getlength (text_blob), dest_offset, src_offset, csid, lang_context, convert_warn); DBMS_LOB.close(text_blob); DBMS_LOB.FreeTEMPORARY(text_blob); -- Событие СREATE IF ora_sysevent = 'CREATE' THEN INSERT INTO TESTER.ddl_log SELECT vddl_id, vver_id, SYSDATE, ora_sysevent, sys_context('USERENV','HOST'), sys_context('USERENV','MODULE'), sys_context('USERENV','OS_USER'), USER, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, EMPTY_CLOB(), 0 FROM DUAL; -- Блокируем строку для апдейта поля типа CLOB SELECT sql_text into temp_clob FROM TESTER.ddl_log WHERE ddl_id = vddl_id AND ver_id = vver_id FOR UPDATE OF sql_text, sql_length; UPDATE TESTER.ddl_log set sql_text = sql_clob, sql_length = dbms_lob.getlength(sql_clob) WHERE ddl_id = vddl_id AND ver_id = vver_id; -- Событие DROP ELSIF ora_sysevent = 'DROP' THEN INSERT INTO TESTER.ddl_log SELECT vddl_id, vver_id, SYSDATE, ora_sysevent, sys_context('USERENV','HOST'), sys_context('USERENV','MODULE'), sys_context('USERENV','OS_USER'), USER, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, EMPTY_CLOB(), 0 FROM DUAL; SELECT sql_text into temp_clob FROM TESTER.ddl_log WHERE ddl_id = vddl_id AND ver_id = vver_id FOR UPDATE OF sql_text, sql_length; UPDATE TESTER.ddl_log set sql_text = sql_clob, sql_length = 0 WHERE ddl_id = vddl_id AND ver_id = vver_id; -- Событие ALTER ELSE -- Для некторых объектов получить программный код после изменения можно только, используя пакет DBMS_METADATA IF ora_dict_obj_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE') THEN sql_clob:=trim(DBMS_METADATA.GET_DDL(ora_dict_obj_type,ora_dict_obj_name,ora_dict_obj_owner,'COMPATIBLE','ORACLE','DDL')); END IF; INSERT INTO TESTER.ddl_log SELECT vddl_id, vver_id, SYSDATE, ora_sysevent, sys_context('USERENV','HOST'), sys_context('USERENV','MODULE'), sys_context('USERENV','OS_USER'), USER, ora_dict_obj_owner, ora_dict_obj_name, ora_dict_obj_type, EMPTY_CLOB(), NULL FROM DUAL; SELECT sql_text into temp_clob FROM TESTER.ddl_log WHERE ddl_id = vddl_id AND ver_id = vver_id FOR UPDATE OF sql_text; UPDATE TESTER.ddl_log SET sql_text = sql_clob, sql_length = dbms_lob.getlength(sql_clob) WHERE ddl_id = vddl_id AND ver_id = vver_id; END IF; END IF; END;
Для включения/октлючения триггера (выполнять под учеткой as sysdba).
ALTER TRIGGER DDL_TRIGGER ENABLE; ALTER TRIGGER DDL_TRIGGER DISABLE;