[Oracle/PLSQL]8. 데이터베이스 트리거 작성.

● 트리거

 

- 데이터베이스 트리거

  연결된 사용자 또는 사용되는 응용 프로그램과 상관없이 INSERT, UPDATE, DELETE 문 등의 트리거 문이 관련 테이블에 대해

  실행될 때 암시적으로 실행된다.

 

- 응용 프로그램 트리거

  응용 프로그램에서 특정 이벤트가 발생할 때마다 암시적으로 실행된다.

 

특정 작업을 수행할 때 관련 작업도 수행하려면 트리거를 사용할 수 있다.

트리거를 과다하게 사용하면 상호 종속성이 복잡해져 대형 응용프로그램에서 트리거를 유지 관리하기가 어렵다.

필요할 때만 트리거를 사용하고 순환 및 계단식 효과를 주의해야한다.

 

 

● 데이터베이스 트리거 구성요소.

 

구성 요소	설명	가능한값
트리거 타이밍	트리거 이벤트에 따라 트리거를 실행하는 시기입니다.	BEFORE  AFTER  INSTEAD OF
트리거 이벤트	트리거를 실행하는 테이블 또는 뷰의 데이터 조작 작업입니다.	INSERT  UPDATE  DELETE
트리거 유형	트리거 본문 실행 횟수입니다.	문  행
트리거 본문	트리거가 수행하는 작업입니다.	모든 PL/SQL 블록

 

1. 트리거 타이밍

 

   트리거가 실행되는 시기.

 

- BEFORE : 테이블의 DML 트리거 이벤트 전에 트리거 본문을 실행한다.

- AFTER   : 테이블의 DML 트리거 이벤트 후에 트리거를 실행한다.

- INSTEAD OF : 트리거 문 대신 트리거 본문을 실행하고 다른 방법으로는 뷰를 수정할 수 없을 경우에 사용한다.

 

2. 트리거 이벤트

 

   트리거를 실행하는 DML문.(INSERT, UPDATE, DELETE)

 

- 트리거 이벤트가 UPDATE면 열 목록을 포함시켜 트리거 실행을 위해 변경해야 할 열을 식별할 수 있다.

  Ex.  . . . UPDATE OF SAL . . .

 

- 트리거 이벤트는 여러 DML 작업을 포함할 수 있다.

  EX.  . . . INSERT or UPDATE or DELETE . . .

        . . . INSERT or UPDATE OF job . . .

 

3. 트리거 유형

 

   트리거 이벤트가 발생할 경우 트리거 본문을 실행하는 횟수에 따라 다음과 같이 구분한다.

 

- 문장트리거 : 영향을 받는 행이 없더라도 트리거 이벤트 대신 문장 트리거를 한 번 실행한다.

                   기본값이다.

                   꼭 1번 실행.

 

- 행트리거    : 테이블이 트리거 이벤트의 영향을 받을 때마다 행 트리거를 실행한다.

                   트리거 이벤트가 행에 영향을 주지 않으면 행 트리거를 실행하지 않는다.

                   1번도 실행하지 않을 수도 있다.

 

4. 트리거 본문

 

   트리거가 수행하는 작업.

   PL/SQL 블록 또는 프로시저에 대한 호출이다.

 

● 문장 트리거 작성 구문

 

CREATE [OR REPLACE] TRIGGER trigger_name

timing

event1 [OR event2 OR event3]

ON table_name

trigger_body

 

trigger_name	트리거의 이름입니다.
timing	트리거 이벤트에 따라 트리거를 실행하는 시기입니다.  BEFORE  AFTER
event	트리거를 실행하는 데이터 조작 작업입니다.  INSERT  UPDATE [OF column]  DELETE
table / view_name	트리거와 관련된 테이블을 나타냅니다.
trigger_body	DECLARE 또는 BEGIN으로 시작하여 END로 끝나며 트리거에 의해 수행되는 작업 또는 프로시저  호출을 정의하는 트리거 본문입니다.

 

Ex. 특정 업무 시간(월-금요일)에만 EMP 테이블에 삽입할 수 있는 트리거

 

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT ON emp
BEGIN
    IF (TO_CHAR (sysdate,'DY') IN ('SAT', 'SUN')) OR
       (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18')
    THEN RAISE_APPLICATION_ERROR (-20500,
                                  'You may only insert into EMP during normal hours.');
    END IF;
END;

 

● 트리거 이벤트 결합 (조건부 술어 사용)

 

    트리거 본문 안에서 특수한 조건부 술어 INSERTING, UPDATING, 및 DELETING을 이용하여 여러 트리거 이벤트를 하나로

    결합할 수 있다.

 

CREATE OR REPLACE TRIGGER secure_emp
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
    IF (TO_CHAR (sysdate, 'DY') IN ('SAT', 'SUN')) OR
       (TO_CHAR (sysdate, 'HH24') NOT BETWEEN '08' AND '18')
    THEN
        IF DELETING
        THEN RAISE_APPLICATION_ERROR (-20502,
                                      'You may only delete from EMP during normal hours.');
            ELSIF INSERTING
            THEN RAISE_APPLICATION_ERROR (-20500,
                                         'You may only insert_into EMP during normal hours.');
            ELSIF UPDATING ('SAL')
            THEN RAISE_APPLICATION_ERROR (-20503,
                                          'You may only update SAL during normal hours.');
            ELSE
                 RAISE_APPLICATION_ERROR (-20504,
                                         'You may only update EMP during normal hours.');
            END IF;
    END IF;
END;

 

● 행 트리거 작성 구문

 

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

[WEHN condition]

trigger_body

 

trigger_name	트리거의 이름입니다.
timing	트리거 이벤트에 따라 트리거를 실행하는 시기입니다.  BEFORE  AFTER
event	트리거를 실행하는 데이터 조작 작업입니다.  INSERT  UPDATE [OF column]  DELETE
table_name	트리거와 관련된 테이블을 나타냅니다.
REFERENCING	현재 행의 기존 값과 새로운 값의 상관 이름을 지정합니다.  (기본값은 OLD와 NEW입니다.)
FOR EACH ROW	트리거를 행 트리거로 지정합니다.
WHEN	트리거 제한  사항을 지정합니다. (이 조건부 술어는 각 행을 평가하여 트리거 본문의 실행 여부를  결정합니다.)
trigger body	DECLARE 또는 BEGIN으로 시작하여 END로 끝나며 트리거에 의해 수행되는 작업 또는 프로시저  호출을 정의하는 트리거 본문입니다.

 

Ex. 특정 사원만 $5,000 이상의 급여를 받을 수 있도록 트리거를 작성.

 

CREATE OR REPLACE TRIGGER DERIVE_COMMISSION_PCT
    BEFORE INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    IF NOT (:NEW.JOB IN ('MANAGER', 'PRESIDENT'))
       AND :NEW.SAL > 5000
    THEN
       RAISE_APPLICATION_ERROR
            (-20202, 'EMPLOYEE CANNOT EARN THIS AMOUNT');
    END IF;
END;

 

● OLD 및 NEW 수식자 사용

 

Ex. EMP 테이블에 대한 사용자 활동을 기록하는 행을 사용자 테이블 AUDIT_EMP_TABLE에 추가한

 

CREATE OR REPLACE TRIGGER audit_emp_values
   AFTER DELETE OR INSERT OR UPDATE
   ON emp
   FOR EACH ROW
BEGIN
   INSERT INTO audit_emp_table (user_name,
                                timestamp,
                                id,
                                old_last_name,
                                new_last_name,
                                old_title,
                                new_title,
                                old_salary,
                                new_salary)
        VALUES (USER,
                SYSDATE,
                :OLD.empno,
                OLD.ename,
                :NEW.ename,
                :OLD.job,
                :NEW.job,
                :OLD.sal,
                :NEW.sal);

END;

 

행 트리거 안에서 OLD 및 NEW 수식자 접두어를 붙여 데이터 변경 전후 열의 값을 참조한다.

 

데이터 작업	OLD 값	NEW 값
INSERT	NULL	삽입된 값
UPDATE	갱신 전의 값	갱신 후의 값
DELETE	삭제 전의 값	NULL

 

- OLD 및 NEW 수식자는 행 트리거에만 사용할 수 있다.

- 모든 SQL 및 PL/SQL 문에서 이들 수식자 앞에는 콜론(:) 접두어가 붙는다.

- WEHN 제한 조건에서 수식자를 참조할 경우에는 콜론(:) 접두어가 붙지 않는다.

 

☞ 행 트리거 제한 (WHEN 사용)

 

Ex. EMP 테이블에 행을 추가하거나 사원 급여를 수정할 경우 EMP 테이블에서 사원의 커미션을 계산하는

    트리거를 작성한다.

 

CREATE OR REPLACE TRIGGER derive_commission_pct
BEFORE INSERT OR UPDATE OF sal ON emp
FOR EACH ROW
WHEN (NEW.job = 'SALESMAN')
BEGIN
    IF INSERTING
        THEN :NEW.comm := 0;
    ELSIF :OLD.comm IS NULL
        THEN :NEW.comm := 0;
    ELSE :NEW.comm := :OLD.comm * (:NEW.sal / :OLD.sal);
    END IF;
END;

 

● INSTEAD OF 트리거

 

본질적으로 갱신할 수 없는 뷰에 대해 DML 문이 실행한 데이터는 INSTEAD OF 트리거를 사용하여 수정한다.

뷰가 둘 이상의 테이블로 구성되어 있을 경우 뷰에 항목을 삽입하면 테이블 하나에는 삽입되고 나머지

테이블은 갱신되므로 뷰에 INSERT 문을 작성할 때 실행할 INSTEAD OF 트리거도 작성한다.

 

CREATE OR REPLACE TRIGGER trigger_name
    INSTEAD OF
        event1 [OR event2 OR event3]

ON view_name

  [REFERENCING OLD AS old | NEW AS new]

[FOR EACH ROW]

trigger_body

 

trigger_name	트리거의 이름입니다.
INSTEAD OF	트리거가 뷰에 사용됨을 나타냅니다.
event	트리거를 실행하는 데이터 조작 작업입니다.  INSERT  UPDATE [OF column]  DELETE
view_name	트리거와 관련된 뷰를 나타냅니다.
REFERENCING	현재 행의 기존 값 및 새로운 값에 대한 상관 이름을 지정합니다.  (기본 값은 OLD 및 NEW 입니다.)
[FOR EACH ROW]	트리거를 행 트리거로 지정합니다. INSTEAD OF 트리거는 행   트리거만 가능하므로 선택적입니다.
trigger body	DECLARE 또는 BEGIN으로 시작하여 END로 끝나며 트리거에  의해 수행되는 작업 또는 프로시저 호출을 정의하는 트리거 본문  입니다.

Ex.

 

create or replace trigger t1_v1_tri
         instead of insert on v1
        begin
            insert into t1 (col1, col2, co3)
        values(substr(:new.col4,1,4),substr(:new.col4,5,2),substr(:new.col4,6,2));
        end;

● 트리거와 내장 프로시저 비교

 

데이터베이스 트리거	내장 프로시저
암시적으로 호출합니다.	명시적으로 호출합니다.
트리거 본문 안에서 COMMIT, ROLLBACK 및 SAVEPOINT 문을  허용하지 않습니다.	프로시저 본문 안에서 COMMIT, ROLLBACK 및 SAVEPOINT  문을 허용합니다.

 

● 트리거 관리

 

- 데이터베이스 트리거 비활성화 또는 재활성화 :

 

  ALTER TRIGGER trigger_name DISABLE | ENABLE

 

- 테이블에 대한 모든 트리거 비활성화 또는 재활성화 :

 

  ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS

 

- 테이블에 대한 트리거 재컴파일 :

 

  ALTER TRIGGER trigger_name COMPILE

 

- 트리거 삭제

 

  DROP TRIGGER secure_emp;