ORACLE|TRIGGER (트리거)

* Insert, Update, Delete이 실행되는 시점 전/후에 동반되어야 하는 작업을 정의한다.

- 자료의 무결성을 유지
- 변경된 자료 및 변경한 유저를 기록해서 테이블의 변경정보 감시
- 지정한 이벤트가(DML)가 발생할때마다 자동으로 실행되는 PL/PQL 블럭
   - 자동 호출(호출문 없음)
- 데이터 흐름 제어문(TCL)은 사용할수 없다!(MS-SQL에서는 가능)
-  제약조건으로 할수없는것은 트리거로 구현한다.
- 제약조건과 트리거가 겹치면 제약조건 우선순위실행이다.

--------------------------------------------------------------------------

※컬럼단위의 이벤트.....   after update of column_name on table_name;
※트리거 정보확인뷰....   user_triggers
※user_objects 뷰를 확인하면 object 에 관한 정보를 볼수있다.

--------------------------------------------------------------------------

사용법1) – row 단위의 변화

CREATE [OR REPLACE] TRIGGER trigger_name

             BEFORE (OR AFTER)

             UPDATE (OR DELETE OR INSERT) ON table_name

             [FOR EACH ROW]

DECLARE

             변수 선언부;

BEGIN

             프로그램 코딩부;

END;

/

사용법2) – column 단위의 변화

CREATE [OR REPLACE] TRIGGER trigger_name

             BEFORE (OR AFTER)

             UPDATE [ OF column1, column2 …] ON table_name

             [FOR EACH ROW]

DECLARE

             변수 선언부;

BEGIN

             프로그램 코딩부;

END;

/

BEFORE : 데이터 처리가 실행되기 전

AFTER : 데이터 처리가 실행 된 후

FOR EACH ROW : 데이터 처리시 건건이 모두 트리거 실행 
- Insert, Delete는 for each row 없인 사용 못함.

:OLD.컬럼명 : SQL 반영전의 컬럼 데이터

:NEW.컬럼명: SQL 반영 후의 컬럼 데이터

RAISE_APPLICATION_ERROR( 에러번호, 에러내용 )
- 강제 에러 처리 (에러번호는 -20000 ~ -20999까지 임의로 사용할 수 있다.)
- rollback 작업을 포함한다.

실례1)

CREATE OR REPLACE TRIGGER REFUSE_98001
             BEFORE
             UPDATE OR DELETE OR INSERT ON PERSONNEL
             FOR EACH ROW
DECLARE

             s_msg   VARCHAR2(100) := '';

BEGIN

             IF UPDATING THEN
                           IF :OLD.EMPNO = '98001' THEN
                                        s_msg := '98001 사번은 수정 할 수 없습니다.!';
                                        raise_application_error( -20001, s_msg);
                           END IF;
             END IF;

             IF DELETING THEN
                           IF :OLD.EMPNO = '98001' THEN
                                        s_msg := '98001 사번은 삭제 할 수 없습니다.!';
                                        RAISE_APPLICATION_ERROR( -20002, s_msg);
                           END IF;
             END IF;

             IF INSERTING THEN
                           IF :NEW.EMPNO = '98001' THEN
                                        s_msg := '98001 사번은 입력 할 수 없습니다.!';
                                        raise_application_error( -20003, s_msg);
                           END IF;
             END IF;

END;

/

SQL> INSERT INTO PERSONNEL(EMPNO, EMP_NAME) VALUES('98001', 'TRIGGER');

결과)

==> Insert

INSERT INTO PERSONNEL(EMPNO, EMP_NAME) VALUES('98001', 'TRIGGER')

            *

1행에 오류:

ORA-20003: 98001 사번은 입력 할 수 없습니다.!

ORA-06512: "DALMA.REFUSE_98001", 줄 19에서

ORA-04088: 트리거 'DALMA.REFUSE_98001'의 수행시 오류

----------------------------------------------------------------------------------

--부서 테이블의 부서가 업데이트되면 해당부서번호를 참조하는 사원들도 함께변경되는 트리거

create or replace trigger t4
    after update of deptno on dept
    for each row
begin
    update sawon set deptno=:new.deptno where deptno=:old.deptno;
end;
/

----------------------------------------------------------------------------------
--사원 테이블에서 급여가 변경되는 경우, 기존 급여보다 적게 변경되거나, 
   20%초과하여 변경되는경우 에러를 발생시켜라.

create or replace trigger t5
    after update of sapay on sawon
    for each row
begin
    if (:old.sapay>:new.sapay) or (:old.sapay*1.2<:new.sapay)  then
        raise_application_error(-20001,'변경범위가맞지않습니다');
    end if;
end;
/

----------------------------------------------------------------------------------

--사원 테이블에서 급여가 변경되는 경우, 기존 급여보다 적게 변경되거나, 
   20%초과하여 변경되는경우 에러를 발생시켜라.
   단 사원이나 대리는 적용되게하라.....(when 절)

create or replace trigger t5
    after update of sapay on sawon
    for each row
   when (old.sajob<>'사원' or old.sajob<>'대리')
begin
    if (:old.sapay>:new.sapay) or (:old.sapay*1.2<:new.sapay)  then
        raise_application_error(-20001,'변경범위가맞지않습니다');
    end if;
end;
/

----------------------------------------------------------------------------------

# 트리거 관리

1) 한테이블의 모든 트리거를 중지시키는 방법

SQL> alter table table_name DISABLED all triggers;

2) 한개 트리거만 변경 (정보 확인 후 Alter를 이용하여 Disable 시킴)

SQL> select trigger_name, table_name, status from user_triggers; 

TRIGGER_NAME                   TABLE_NAME                     STATUS
------------------------------ ------------------------------ --------
T4                             DEPT                           ENABLED
T5                             SAWON                          ENABLED
T8                             LOAN                           ENABLED

SQL> alter trigger t4 disable;

트리거가 변경되었습니다.

[출처] ORACLE Trigger|작성자 포비코난