dokuwiki

table

create table schema_object_log (
   id         number,
   actiondate date,
   username   varchar2(100),
   action     varchar2(100),
   objecttype varchar2(100),
   objectname varchar2(100)
   );

trigger

CREATE OR REPLACE TRIGGER CREATE_TABLE_TRG
 BEFORE 
 CREATE OR ALTER OR DROP
 ON HR.SCHEMA
declare
  e_invalid_table_name exception;

  pragma exception_init
    (e_invalid_table_name, -20001);

begin
  dbms_output.put_line(ora_sysevent||' '||
                       ora_dict_obj_owner ||' '||
                       ora_dict_obj_name ||' '||
                       ora_dict_obj_type);
                       
if ora_dict_obj_type = 'TABLE' and
   not REGEXP_LIKE(ora_dict_obj_name, '^[[:alpha:]]{3}_[[:alpha:]]{1,}') and
   ora_sysevent in ('CREATE', 'ALTER')
   then
   raise_application_error(-20001, 'table name must look like abc_d');
end if;


insert into schema_object_log (
   id,
   actiondate,
   username,
   action,
   objecttype,
   objectname) values (
   hr_seq.NEXTVAL,
   sysdate,
   ora_dict_obj_owner,
   ora_sysevent,
   ora_dict_obj_type,
   ora_dict_obj_name);

end;