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;
/
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;
/
No comments:
Post a Comment