본문으로 건너뛰기

BEFORE와 AFTER 트리거

급여 상한 검사와 변경 이력 예제로 비교하는 BEFORE 및 AFTER 트리거의 실행 시점

CREATE OR REPLACE TRIGGERBEFOREAFTERUPDATE OF:NEW:OLDRAISE_APPLICATION_ERRORINSERTINGUPDATINGDELETING

#핵심 요약

트리거는 등록된 DML 이벤트가 일어나면 자동으로 실행됨
별도로 EXEC할 필요가 없음

BEFORE UPDATE OF sal은 급여 컬럼을 바꾸기 전에 실행됨
실습에서는 여기서 :NEW.sal을 검사해 9000 이상이면 RAISE_APPLICATION_ERROR로 변경을 막았음

AFTER INSERT OR UPDATE OR DELETE는 DML이 행에 적용된 뒤 실행됨
실습에서는 여기서 변경 이력 테이블에 INSERT했음

트리거는 직접 SQL이든 프로시저든 애플리케이션이든 어느 경로로 DML이 들어와도 실행됨
이행에서 트리거를 빠뜨리면 업무 규칙이 조용히 사라짐

#개념과 동작 원리

실습 트리거	시점	단위	용도
`trg_emp_t_bu`	BEFORE UPDATE OF sal	행(`FOR EACH ROW`)	급여 상한 검사
`trg_emp_comm`	BEFORE UPDATE OF comm	행	커미션 상한 검사
`trg_emp_t_aiud`	AFTER INSERT OR UPDATE OR DELETE	행	변경 이력 기록
`trg_emp_t_bd`	BEFORE DELETE	문장	이행 기간 삭제 금지

:NEW는 바뀐 뒤 값, :OLD는 바뀌기 전 값임
행 단위와 문장 단위의 차이는 행 트리거와 문장 트리거, 이력 트리거의 세부는 변경 이력과 입력값 처리에 있음

실습 비교: 하나의 트리거로 여러 DML을 받을 때 Oracle은 INSERTING, UPDATING, DELETING 조건으로 어떤 DML인지 구분함
PostgreSQL은 TG_OP를 쓰고, MySQL은 DML마다 트리거를 따로 만듦

#필수 명령어 또는 SQL

#BEFORE 행 트리거: 급여 상한 검사

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE TRIGGER trg_emp_t_bu
BEFORE UPDATE OF sal ON emp_t
FOR EACH ROW
BEGIN
  IF :NEW.sal >= 9000 THEN
    RAISE_APPLICATION_ERROR(-20602, :OLD.ename || ' 급여를 9000 이상으로 바꿀 수 없습니다.');
  END IF;
END;
/

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

UPDATE emp_t SET sal = 9500 WHERE empno = 7839;     -- 결과: ORA-20602
EXEC p_raise_sal(7839, 100)                         -- 결과: 5000 → 10000 시도, ORA-20602
SELECT ename, sal FROM emp_t WHERE empno = 7839;    -- 결과: 5000 그대로

결과 해석: 직접 UPDATE와 프로시저를 거친 UPDATE가 똑같이 막힘
트리거가 오류를 내면 그 UPDATE 문은 실패함

#커미션 상한

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE TRIGGER trg_emp_comm
BEFORE UPDATE OF comm ON emp
FOR EACH ROW
BEGIN
  IF :NEW.comm >= 8000 THEN
    RAISE_APPLICATION_ERROR(-20603, :OLD.ename || ' 커미션을 8000 이상으로 바꿀 수 없습니다.');
  END IF;
END;
/

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

UPDATE emp SET comm = 9000 WHERE empno = 7839;      -- 결과: ORA-20603

결과 해석:

이 답은 실습 테이블 emp_t가 아니라 일반 실습 테이블 emp에 트리거를 걺
실습 뒤 지우지 않으면 이후 emp 실습에서도 계속 동작함

#AFTER 행 트리거: 변경 이력

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE TABLE emp_t_audit
( empno    NUMBER,
  ename    VARCHAR2(20),
  action   VARCHAR2(10),
  old_sal  NUMBER,
  new_sal  NUMBER,
  chg_user VARCHAR2(30),
  chg_date DATE );

CREATE OR REPLACE TRIGGER trg_emp_t_aiud
AFTER INSERT OR UPDATE OR DELETE ON emp_t
FOR EACH ROW
DECLARE
  v_action VARCHAR2(10);
BEGIN
  IF INSERTING THEN
    v_action := 'INSERT';
  ELSIF UPDATING THEN
    v_action := 'UPDATE';
  ELSE
    v_action := 'DELETE';
  END IF;

  INSERT INTO emp_t_audit
  VALUES (NVL(:NEW.empno, :OLD.empno),
          NVL(:NEW.ename, :OLD.ename),
          v_action, :OLD.sal, :NEW.sal, USER, SYSDATE);
END;
/

결과 해석:

실행과 결과 해석은 변경 이력과 입력값 처리에 있음