User Tools

Site Tools


kurs:schema_trigger

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

kurs:schema_trigger [2014/09/10 21:22]
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>​
 +
  
kurs/schema_trigger.txt ยท Last modified: 2014/09/10 21:22 (external edit)