This shows you the differences between two versions of the page.
— |
kurs:schema_trigger [2014/09/10 21:22] (current) |
||
---|---|---|---|
Line 1: | Line 1: | ||
+ | table | ||
+ | <code> | ||
+ | create table schema_object_log ( | ||
+ | id number, | ||
+ | actiondate date, | ||
+ | username varchar2(100), | ||
+ | action varchar2(100), | ||
+ | objecttype varchar2(100), | ||
+ | objectname varchar2(100) | ||
+ | ); | ||
+ | </code> | ||
+ | trigger | ||
+ | <code> | ||
+ | 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; | ||
+ | </code> | ||
+ | |||