[Oracle] CH9 : 트리거

트리거

어떠한 이벤트를 Trigger로 하여 자동으로 기동되는 프로시저

EX) 어떠한 테이블에 UPDATE문이 실행되면 트리거(INSERT문)가 기동되어 로그 테이블에 기록됨

---

트리거의 사용 목적

- 복잡한 보안 요건 구현

  EX) 중요한 테이블에 대해 특정 데이터로 변경할 수 없거나 특정 컬럼은 변경을 허용하지 않음 등의 처리 실행 가능

- 높은 수준의 감사

  테이블에 수행된 변경 내용을 기록하기 위해 트리거 사용

  데이터베이스 처리나 특정 사용자에 대한 처리가 수행된 경우에 기동하는 트리거를 작성하면, 어떤 사용자가 어떤 일을 했는지 기록 가능

- 복잡한 규칙 설정

  사용자가 규칙을 정의할 때는 보통 CHECK 제약 등의 무결성 제약 조건을 사용하지만, 트리거를 사용하면 '급여액을 30% 이상 감액하는 업데이트 금지', '입사일은 오늘 이전의 날짜' 등의 복잡한 규칙 정의 가능

---

트리거 종류

종류	개요
DML 트리거	테이블에 대한 DML(INSERT, UPDATE, DELETE)로 기동
INSTEAD OF 트리거	뷰에 대한 DML 조작으로 기동
DDL 트리거	DDL(CREATE, ALTER, DROP) 으로 기동
이벤트 트리거	데이터베이스 처리나 특정 사용자의 처리로 기동

---

트리거 타입

- 트리거가 기동되는 타이밍

종류	개요
BEFORE 트리거	DML문이 실행되기 전에 트리거를 기동
AFTER 트리거	DML문이 실행된 후에 트리거를 기동

- 트리거를 기동하는 단위

종류	개요
문장 트리거(Statement Trigger)	DML문에 대해 한 번만 기동함
행 트리거(Row Trigger)	DML문이 영향을 주는 행마다 기동함

 

트리거의 조합

	문장 트리거 (Statement Trigger)	행 트리거 (Row Trigger, FOR EACH ROW)
BEFORE 트리거	BEFORE 문장 트리거. DML 문의 실행 전에 한 번만 기동함	BEFORE 행 트리거. DML문에 의해 영향을 받는 각 행에 대해 DML문이 실행되기 전에 트리거를 기동
AFTER 트리거	AFTER 문장 트리거. DML문의 실행 후 한 번만 기동	AFTER 행 트리거. DML문에 의해 영향을 받는 각 행에 대해 DML문이 실행된 후 트리거를 기동

---

트리거 작성

CREATE [OR REPLACE] TRIGGER <트리거명> -- 정의부
  { BEFORE | AFTER }
  { DELETE | INSERT | UPDATE [OF <컬럼명>]} ON <테이블명>
  [ FOLLOWS <트리거명>[, <트리거명> ...]]
  [{ENABLE|DISABLE}]
  [FOR EACH ROW [WHEN <조건>]]
DECLARE                               -- 본체
[<선언부>]
BEGIN
  <실행부>
[EXCEPTION
  <예외처리부>]
END;

파라미터	개요
BEFORE, AFTER	트리거가 기동하는 타이밍을 지정
OF <컬럼명>	UPDATE문을 지정한 경우 컬럼 리스트를 포함할 수 있음. 컬럼 리스트를 지정하면 트리거를 지정된 컬럼이 갱신된 경우에만 기동. 여러 컬럼을 기동 조건으로 하는 것도 가능하며, 이 경우 콤마(,)로 구분하여 지정
FOLLOWS	같은 테이블에 동일한 타입의 여러 트리거를 만들 때, 실행 순서 지정 가능. 관련된 모든 트리거에 대해 직전에 실행하는 트리거를 지정. FOLLOWS에서 지정하는 트리거는 이미 존재하고 있는 것이어야 함.
ENABLE, DISABLE	사용 가능, 또는 사용 불가능으로 트리거를 작성할 수 있음.
FOR EACH ROW	트리거가 기동되는 단위가 행 트리거가 됨. 지정하지 않으면 구문(Statement) 트리거가 됨.
WHEN절	트리거를 기동하는 조건을 지정. 행 트리거를 지정한 경우에만 지정 가능. 대상이 되는 각 행의 조건 확인 필요

 

-- 일요일에 EMP 테이블을 변경하면 오류를 발생시키는 트리거
CREATE OR REPLACE TRIGGER emp_nomod
BEFORE INSERT OR DELETE OR UPDATE ON emp -- DML문이 EMP 테이블에 수행되기 전 기동
BEGIN  -- 문장 트리거를 작성하기 위해 FOR EACH ROW 설정 X
    IF (TO_CHAR(SYSDATE, 'DY') = '일') THEN -- 일요일인 경우 오류 반환
        IF DELETING THEN
            raise_application_error(-20325, '일요일에 데이터 삭제 불가');
        ELSIF INSERTING THEN
            raise_application_error(-20326, '일요일에 데이터 추가 불가');
        ELSIF UPDATING THEN
            raise_application_error(-20327, '일요일에 데이터 삭제 불가');
        ELSE
            raise_application_error(-20328, '일요일에 EMP 테이블의 변경 불가');
        END IF;
    ELSE NULL;
    END IF;
END;
/

트리거가 생성되었습니다.

UPDATE emp SET sal = 1000 WHERE empno = 7369;
UPDATE emp SET sal = 1000 WHERE empno = 7369
      *
1행에 오류:
ORA-20327: 일요일에 데이터 삭제는 할 수 없습니다.
ORA-06512: "SCOTT.EMP_NOMOD", 10행
ORA-04088: 트리거 'SCOTT.EMP_NOMOD'의 수행시 오류

---

상관 관계명 old와 new

- 트리거에서는 old와 new 라는 상관 관계명을 사용하여 트리거를 기동한 DML문 처리에 대한 변경 전후의 값을 조회할 수 있음.

- 상관 관계명의 사용은 대상 행의 컬럼 값에 액세스하기 위한 것이므로 행 트리거에서만 조회 가능

- 트리거 본문에서 사용하는 경우, 콜론(:)을 붙여야 함(:new, :old)

-- 상관 관계명 old와 new를 사용하여 EMP 테이블에 대한 변경 정보(변경 전후의 값)를
-- 감사 테이블인 AUDIT_TABLE에 저장하는 트리거
CREATE OR REPLACE TRIGGER modify_emp
AFTER INSERT OR UPDATE OR DELETE ON emp -- INSERT문, DELETE문, UPDATE문 중 하나의 DML문이 EMP 테이블에 실행된 후 기동됨.
FOR EACH ROW-- 상관 관계명 old와 new는 행 트리거에서만 사용할 수 있으므로 FOR EACH ROW를 지정하여 행 트리거를 작성
BEGIN
    IF INSERTING THEN
        INSERT INTO audit_table -- 취득한 값은 감사 내용으로써 감사 테이블 AUDIT_TABLE에 저장
            VALUES('EMP', 'INSERT', :new.empno);
    ELSIF DELETING THEN
        INSERT INTO audit_table
            VALUES('EMP', 'DELETE', :old.empno);
    ELSIF UPDATING THEN
        INSERT INTO audit_table
            VALUES('EMP', 'UPDATE', :old.empno);
    END IF;
END;
/

트리거를 생성되었습니다.

-- 트리거 modify_emp의 실행
DELETE FROM emp WHERE empno = 7369;

1행이 삭제되었습니다.

SELECT * FROM audit_table; -- AUDIT_TABLE에 로그 저장되므로 트리거의 기동 여부 확인 가능

TABLENAME    ACTION     MOD_VAL
-----------  --------   ---------
EMP          DELETE     7369

- 상관 관계명 old와 new를 사용하면 변경 전후의 값을 참조하는 것뿐만 아니라 갱신도 가능

- 상관 관계명을 사용한 값 참조는 BEFORE 트리거, AFTER 트리거 양쪽 모두 할 수 있지만, 값의 갱신은 BEFORE 트리거에서만 가능

- UPDATE 문에서는 새로운 컬럼 값과 원래 컬럼 값을 확인할 수 있음

- INSERT 문에서는 원래 컬럼 값은 존재하지 않아 NULL값이 되므로 새로운 컬럼 값의 확인만 가능

- DELETE 문에서는 원래 컬럼 값에 액세스 가능

 

상관 관계명 old와 new의 반환 값 목록

	New	Old
INSERT	INSERT된 값	NULL
UPDATE	UPDATE된 값	UPDATE되기 전의 값
DELETE	NULL	DELETE되기 전의 값

---

트리거와 트랜잭션

트리거 내에서는 DML문이 수행하는 처리와 트리거가 수행하는 처리가 동일한 트랜잭션에서 수행되기 때문에 , DDL문과 트랜잭션 제어문(COMMIT, ROLLBACK문)이 기술되어 있으면 사용자가 알지 못하는 곳에서 트랜잭션이 종료되기 때문에 DDL문과 트랜잭션 제어문(COMMIT, ROLLBACK문)은 기술할 수 없음

 

-- 트리거의 트랜잭션
DELETE FROM emp WHERE empno = 7369; -- 트리거를 실행하면 감사 테이블 AUDIT_TABLE에 감사 로그 저장

1 행이 삭제되었습니다.

SLEECT * FROM audit_table;

TABLE_NAME   ACTION    MOD_VAL
----------  --------   -------
EMP          DELETE    7369


ROLLBACK; -- 트리거 처리가 동일한 트랜잭션에서 이루어짐

롤백이 완료되었습니다.


SELECT * FROM audit_table;

선택된 레코드가 없습니다.

트랜잭션이 롤백되어도 이력을 감사 로그로 남겨두고 싶을 경우에는 트리거를 기동시킨 트랜잭션과 트리거를 처리하는 트랜잭션을 다른 트랜잭션으로 설정할 필요가 있음 -> 자율형 트랜잭션

 

Oracle의 감사 기능 목록

감사 기능	개요
필수 감사	관리 사용자로 Oracle에 연결한 경우, 또는 Oracle의 기동, 정지를 수행한 경우에 항상 디폴트로 감사하는 기능
DBA(데이터베이스 관리자) 감사	DBA가 수행한 작업에 대해 감사하는 기능. 이를 통해 관리 사용자가 어떠한 조작을 수행하였는지를 조사하는 것이 가능.
데이터베이스 감사	오브젝트 레벨(테이블, 뷰 등)에서 대상 오브젝트에 대해, 어떤 사용자가 어떤 조작을 수행했는가를 감사하는 기능. 다음 세 레벨에서 감사 작업 수행 - SQL문 레벨 - 시스템 권한 레벨 - 오브젝트 레벨
Fine-Grained 감사	오브젝트(테이블, 뷰) 내의 특정 컬럼에 대한 SELECT문, UPDATE문, DELETE문, INSERT문의 감사를 수행하는 기능. 예를 들어 WHERE절 조건에 따라 감사 수행 여부를 설정할 수 있음

---

WHEN절 사용

WHEN절을 사용하여 트리거의 기동 조건을 세세하게 지정 가능

ㄴ> 불필요하게 트리거가 기동되어 발생하는 오버 헤드를 줄일 수 있음

-- 트리거 check_sal
CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN(new.sal/old.sal <= 0.7)
BEGIN
    raise_application_error(-20005, '한계 값을 초과한 감봉입니다.');
END;
/

트리거가 생성되었습니다.



-- 트리거 check_sal 실행
UPDATE emp SET sal = 500 WHERE empno = 7369; -- UPDATE문을 실행하여 SAL컬럼의 값을 변경하려하지만
UPDATE emp SET sal = 500 WHERE empno = 7369 -- 지정된 한계값을 초과하여 트리거 실행 -> 오류 발생
      *
1행에 오류:
ORA-20005: 한계 값을 초과한 감봉입니다.
ORA-06512: "SCOTT.CHECK_SAL", 2행
ORA-04088: 트리거 'SCOTT.CHECK_SAL'의 수행시 오류

---

트리거의 상태 변경

트리거의 용도

- 보안 검사 및 감사

- 배치(Batch) 처리 실행 (트리거를 일시적으로 비활성화)

ALTER TRIGGER <트리거명> {ENABLE|DISABLE};

종류	개요
ENABLE	트리거를 사용할 수 있게 함
DISABLE	트리거를 사용할 수 없게 함

 

복수의 트리거를 사용할 수 없게 하는 경우에 트리거 개수만큼 ALTER TRIGGER문 실행 필요

ㄴ> 특정 테이블마다 정의된 모든 트리거의 상태를 일괄적으로 변경하는 방법 필요

ㄴ> ALTER TABLE문에 ALL TRIGGERS를 지정하여 특정 테이블에 대한 모든 트리거의 상태를 일괄적으로 변경 가능

ALTER TALBE <트리거명> {ENABLE|DISABLE} ALL TRIGGERS;

---

트리거의 관리

데이터 딕셔너리 USER_TRIGGERS 뷰 : 어떤 테이블에 정의되어 있는 트리거 확인

ㄴ 트리거가 기동되는 타이밍과 단위, 현재 트리거의 사용 가능 여부 등의 정보 확인 가능

SELECT trigger_name, trigger_type, table_name, status FROM user_triggers;

TRIGGER_NAME   TRIGGER_TYPE           TABLE_NAME       STATUS
------------   --------------------   --------------   ----------
CHECK_SAL      BEFORE EACH ROW         EMP              ENABLED
MODIFY_EMP     AFTER EACH ROW          EMP              ENABLED
EMP_NOMOD      BEFORE STATEMENT        EMP              ENABLED

USER_TRIGGERS 뷰: 사용자가 소유한 트리거의 상세 정보 표시

컬럼명	개요
TRIGGER_NAME	트리거명
TRIGGER_TYPE	트리거를 기동하는 타이밍
TRIGGERING_EVENT	트리거를 기동하는 이벤트
TRIGGER_OWNER	트리거가 정의된 테이블의 소유자
TABLE_NAME	트리거가 정의된 테이블명, 또는 뷰명
STATUS	트리거의 상태
DESCRIPTION	트리거의 설명
TRIGGER_BODY	기동 시에 트리거가 실행할 한 개 이상의 구문

---

트리거의 종속

트리거의 종속(Cascade) : 트리거의 처리에 의해 다른 트리거가 기동되는 상태

Oracle의 트리거 종속은 최대 32개까지로 제한되어 있음

---

트리거의 실행 권한

트리거는 자동적으로 기동되기 때문에 EXECUTE 권한 존재하지 않음

기동되는 동기가 될 DML처리를 수행할 권한이 있으면 다른 권한 요구 없이 트리거 자동 기동

트리거는 EXECUTE 명령어나 PL/SQL 블록에서 명시적으로 실행 가능

 

 

 

 

참조 : 책 '전문가를 위한 오라클 PL/SQL 입문'