[Oracle/PLSQL]9. 트리거 개념 추가 정보

트리거 본문을 코딩하기 전에 트리거의 구성 요소를 결정해야 한다.

시스템 이벤트에 대한 트리거는 데이터베이스 또는 스키마 레벨에서 정의할 수 있다.

예를 들어, 데이터베이스 종료 트리거는 데이터베이스 레벨에서 정의하며 DDL 문의 트리거 및 사용자

로그온 / 로그아웃 데이터베이스 레벨 또는 스키마 레벨에서 정의할 수 있다.

DML문의 트리거는 특정 테이블 또는 뷰에서 정의한다.

 

데이터베이스 레벨에서 정의한 트리거는 모든 사용자에 대해 실행되지만 스키마 레벨이나 테이블 레벨에서 정의한 트리거는

이벤트가 해당 스키마 또는 테이블과 관련된 경우에만 실행된다.

 

● DDL 문에 트리거 작성

 

CREATE [OR REPLACE] TRIGGER trigger_name

timing

[ddl_event1 [OR ddl_event2 OR ...]]

ON {DATABASE | SCHEMA}

trigger_body

 

DDL_Event	가능한 값
CREATE	CREATE 문이 딗너리에 새 데이터베이스 객체를 추가할 때마다  트리거를 실행합니다.
ALTER	ALTER 문이 데이터 딕셔너리에 있는 데이터베이스 객체를 수정  할 때마다 트리거를 실행합니다.
DROP	DROP 문이 데이터 딕셔너리에서 데이터베이스 객체를 제거할  때마다 트리거를 실행합니다.
트리거 본문	전체 PL/SQL 블록입니다.

 

● 시스템 이벤트 트리거 작성

 

CREATE [OR REPLACE] TRIGGER trigger_name

timing

[database_event1 [OR database_event2 OR ...]]

ON {DATABASE | SCHEMA}

trigger_body

 

Database_Event	가능한 값
AFTER SERVERERROR	서버 오류 메시지가 기록될 때마다 트리거를 실행한다.
AFTER LOGON	사용자가 데이터베이스에 로그온할 때마다 트리거를 실행한다.
BEFORE LOGOFF	사용자가 데이터베이스에서 로그아웃할 때마다 트리거를  실행한다.
AFTER STARTUP	데이터베이스가 열릴 때마다 트리거를 실행한다.
BEFORE SHUTDOWN	데이터베이스가 종료될 때마다 트리거를 실행한다.

 

● 로그온 및 로그아웃 트리거 예제

 

CREATE OR REPLACE TRIGGER LOGON_TRIG
   AFTER LOGON
   ON SCHEMA
BEGIN
   INSERT INTO log_trig_table (user_id, log_date, action)
        VALUES (USER, SYSDATE, 'Logging on');
END;

 

이 트리거를 작성하여 로그온 및 로그오프 빈도를 모니터하거나 총 로그온 시간에 대한 보고서를 작성할 수 있는데 사용자가

DBA인 경우 이 작업을 수행하려면 SCHEMA를 DATABASE로 바꾼다.

 

● CALL 문

 

CALL 문을 사용하면 트리거 자체에 PL/SQL 본문을 코딩하는 대신 내장 프로시저를 호출할 수 있다.

 

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1 [OR event2 OR event3]

ON table_name

     [REFERENCING OLD AS old | NEW AS new]

[FOR EACH ROW]

[WHEN condition]

CALL procedure_name

 

Ex.

 

CREATE TRIGGER TEST3

BEFORE INSERT ON EMP

CALL LOG_EXECUTION

 

● 변경 테이블 예제

 

- 변경테이블 : 트리거된 테이블 자체 및 FOREIGN KEY 제약 조건을 통해 트리거된 테이블을 참조하는 테이블은

                   모두 변경 테이블이다.

 

 

Ex. 신입사원이 EMP 테이블에 추가되거나 기존 사원의 급여 또는 직위가 변경될 때마다 사원 급여가 사원 직위에

    해당되는 급여 범위에 포함되도록 할 수 있다.

 

CREATE OR REPLACE TRIGGER check_salary
   BEFORE INSERT OR UPDATE OF sal, job
   ON emp
   FOR EACH ROW
   WHEN (new.job <> 'PRESIDENT')
DECLARE
   v_minsalary   emp.sal%TYPE;
   v_maxsalary   emp.sal%TYPE;
BEGIN
   SELECT MIN (sal), MAX (sal)
     INTO v_minsalary, v_maxsalary
     FROM emp
    WHERE job = :new.job;

   IF :new.sal < v_minsalary OR :new.sal > v_maxsalary
   THEN
      RAISE_APPLICATION_ERROR (-20505, 'Out of range');
   END IF;
END;