093 - [Oracle PL/SQL] DDL triggers ( Schema or Database )

이전까지는 테이블이나 뷰를 대상으로 트리거에 대해서 알아보았습니다.

이번에는 스키마, 데이터베이스에 대한 트리거에 대해서 알아보겠습니다.

 

 

 

 

 

• 스키마 트리거 생성 - 특정 시간에만 create 명령어를 수행할 수 있음

CREATE OR REPLACE TRIGGER before_create_trigger
BEFORE CREATE ON SCHEMA -- CREATE/ALTER/DROP
BEGIN
    if  to_number(to_char(sysdate,'hh24')) not between 8 and 16 then
        raise_application_error(-20001, 'Create not Allowed now');
    end if;
END;

 

 

 

• 테스트 결과 - 현재 시간이 8-16시 사이가 아니여서 에러 발생

create table  t2 (value1 number );
----------------------------------------------------------------
Error report -
ORA-04088: error during execution of trigger 'HR.BEFORE_CREATE_TRIGGER'
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Create not Allowed now
ORA-06512: at line 3
04088. 00000 -  "error during execution of trigger '%s.%s'"
*Cause:    A runtime error occurred during execution of a trigger.
*Action:   Check the triggers which were involved in the operation.


-- 테스트 이후에는 삭제한다
drop trigger before_create_trigger;

 

 

 

• DDL 작업 로그를 작성하는 트리거 샘플 - 준비작업

drop table ddl_log;
/
CREATE TABLE ddl_log (
 operation   VARCHAR2(30),
 obj_owner   VARCHAR2(30),
 object_name VARCHAR2(30),
 attempt_by  VARCHAR2(30),
 attempt_dt  DATE);

 

 

 

• 트리거 생성

CREATE OR REPLACE TRIGGER before_create_trigger
after DDL ON SCHEMA
BEGIN
   INSERT INTO ddl_log
   SELECT ora_sysevent, ora_dict_obj_owner,
   ora_dict_obj_name, USER, SYSDATE
   FROM DUAL;
END;

 

 

 

• 테스트 결과 - 스키마의 ddl 내용을 모두 저장 가능

select * from ddl_log;
------------------------------------------
no rows selected


create table tabl1( n number );


select * from ddl_log;
------------------------------------------
OPERATION       OBJ_OWNER  OBJECT_NAME     ATTEMPT_BY      ATTEMPT_D
--------------- ---------- --------------- --------------- ---------
CREATE          HR         TABL1           HR              08-MAY-24


alter table tabl1 add x number;


select * from ddl_log;
------------------------------------------
OPERATION       OBJ_OWNER  OBJECT_NAME     ATTEMPT_BY      ATTEMPT_D
--------------- ---------- --------------- --------------- ---------
CREATE          HR         TABL1           HR              08-MAY-24
ALTER           HR         TABL1           HR              08-MAY-24


drop table tabl1;


select * from ddl_log;
------------------------------------------
OPERATION       OBJ_OWNER  OBJECT_NAME     ATTEMPT_BY      ATTEMPT_D
--------------- ---------- --------------- --------------- ---------
CREATE          HR         TABL1           HR              08-MAY-24
ALTER           HR         TABL1           HR              08-MAY-24
DROP            HR         TABL1           HR              08-MAY-24