oracle - 오라클 트리거

오라클 트리거 

 

오라클 트리거(Trigger)는 트리거링 사건(Triggering Event)에 의해 자동으로 실행되는 PL/SQL 이라고 할 수 있다.

트리거링 사건은 데이터베이스의 테이블에 DML 문이 실행될때 발생하는데 INSERT,UPDATE,DELETE문의 사용에 트리거링 사건을 정의 할 수 있고, 이들이 실행될때 트리거가 자동으로 실행된다.

예) emp테이블에 sal 컬럼은 사원의 봉급을 나타낸다. 누군가가 이 봉급을 자기가 원하는 값으로 변경시킨다면 문제가 될 것이다.

이런 일을 방지하기위해 트리거를 사용할 수 있다. sal 컬럼의 값이 update 되면 그 기록을 남기도록 할 수 있는것이다.

트리거는 다음과 같은 작업에 사용될 수 있다.

⊙ 데이터베이스 테이블 생성시 참조 무결성과 데이터 무결성, 그밖의 다른 제약조건으로 정의할 수 없는 복잡한 요구 사항에 대한 제약 조건을 생성할 수 있다.

⊙ 데이터베이스 테이블의 데이터에 생기는 작업을 감시, 보안할 수 있다.

⊙ 데이터베이스 테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행시킬 수 있다.

트리거 구조

CREATE [OR REPLACE] TRIGGER tigger_name BEFORE | AFTER

triggering-event ON table_name

[FOR EACH ROW]

[WHEN (condition)]

PL/SQL black

문장트리거와 행 트리거

트리거는 문장 트리거(Statement-Level Trigger) 와 행 트리거(Row-Level Trigger)로 구분하며, FOR EACH ROW 옵션절의 사용 여부로 구분된다. FOR EACH ROW 옵션절을 사용한 트리거는 행 트리거가 된다.

 

문장트리거

트리거링 사건에 의해 단 한번 실행되며, 컬럼의 각 데이터 행을 제어할 수 없다. 따라서 문장 트리거는 컬럼의 데이터값에 상관없이 그 컬럼에 변화가 일어나는 것을 감지하여 실행되는 트리거이다.

 

행트리거

컬럼의 각각의 데이터 행에 변화가 생길 때마다 실행되며, 그 데이터 행의 실제값을 제어할 수 있다. 따라서 행 트리거는 데이터 행의 실제값을 수정,변경 또는 저장할 때 사용이 가능하다.

 

예) DELETE * FROM EMP WHERE SAL > 2000; 이라는 SQL문이 실행되어 해당하는 4개의 ROW가 삭제되었다고 할때 

문장트리거는 1번 실행되며, 행트리거는 ROW의 갯수와 같은 4번 실행된다.(행트리거는 각각의 ROW에 대해 실행된다.)

행트리거의 컬럼값 참조

행트리거에서 컬럼의 실제 데이터값을 제어하는데 사용되는 연산자는 " :old " 와 " :new " 이며, 이들은 다음과 같이 각 SQL문에 따라 사용이 허락된다.

■ INSERT 문의 경우 입력할 데이터값은 new.column_name 에 지정된다.  INSERT문에는 old 값이 존재하지 않는다.

■ UPDATE 문의 경우 변경전의 컬럼 데이터값은 old.column_name 으로 나타내며, 수정할 새로운 값은 new.column_name에 지정할 수있다.

■ DELETE 문의 경우 삭제되는 컬럼값은 old.column_name 에 저장된다. DELETE 문에는 new 값이 존재하지 않는다.

트리거링 사건

INSERT, UPDATE, DELETE 의 DML 문이 실행될 때 자동 실행되며, BEFORE 또는 AFTER 연산자와 함께 사용된다.

예를 들어 BEFORE INSERT 트리거는 INSERT문이 실행되기 전에 실행되며, AFTER INSERT 트리거는 INSERT문이 실행된 후에 실행된다.

주의사항

1) TRIGGER 내에서는 TRANSACTION 제어문(COMMIT,ROLLBACK,SAVEPOINT)을 사용할 수 없다.

    TRIGGERING 을 유발한 DML 의 TRANSACTION에 종속되기 때문임.

2) TRIGGER 자체도 내부적인 PROCESSING 을 유발하기 때문에 큰 테이블에 대한 트리거는 수행속도에 영향을 미친다.

 

 

SQL> ED TRIGGER.SQL

CREATE OR REPLACE TRIGGER chk_sal_emp

BEFORE UPDATE OF SAL ON emp FOR EACH ROW

BEGIN

         INSERT INTO CHG_SAL_LOG

         VALUES(TO_CHAR(SYSDATE,'YYYY-MM-DD:HH24MISS'), 

                      SUBSTR(USER,1,20),:OLD.EMPNO,:OLD.SAL,:NEW.SAL);

END;

/

 

 

SET SERVEROUTPUT ON

CREATE OR REPLACE TRIGGER emp_before_insert BEFORE

INSERT ON emp

FOR EACH ROW

DECLARE

TOO_BIG EXCEPTION;

BEGIN

IF :NEW.SAL > 10000 THEN

RAISE TOO_BIG;

END IF;

EXCEPTION

WHEN TOO_BIG THEN

RAISE_APPLICATION_ERROR(20002, 'SAL의 값이 허용치를 초과했습니다.');

END;

/