Tuesday, May 19, 2015

DDL trigger that will fire on create table

11:15 AM Posted by Dilli Raj Maharjan No comments
create or replace trigger
DDLTrigger
AFTER create or drop ON schema
declare
TRIG_NAME varchar2(32):='TRG_' || ora_dict_obj_name;
BEGIN
    if ora_sysevent='CREATE' and ora_dict_obj_type='TABLE'  and ora_dict_obj_name != 'MY_DML_LOG'  THEN
        Execute immediate('CREATE OR REPLACE TRIGGER ' || TRIG_NAME ||
            ' BEFORE INSERT OR UPDATE OR DELETE ON ' || ora_dict_obj_owner || '.' || ora_dict_obj_name ||
            ' declare
            begin
            if UPDATING then
                insert into my_dml_log values(''UPDATE'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            elsif DELETING then
                insert into my_dml_log values(''DELETE'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            elsIF INSERTING then
                insert into my_dml_log values(''INSERT'',sysdate ,''' || ora_dict_obj_owner ||''','''|| ora_dict_obj_name || ''');
            end if;
            end;');
        END IF;
END;
/


0 comments:

Post a Comment