[ORACLE] 트리거 사용 예시

트리거 기본 문법

---

CREATE [OR REPLACE] TRIGGER 트리거이름
[AFTER/BEFORE] [update or delete]
    ON 테이블 이름
    [FOR EACH ROW] /*행 트리거 여부를 결정, DML의 영향을 받는 레코드마다 트리거 동작*/
    [WHEN 조건]
DECLARE
	선언부
 BEGIN
 	트리거 실행코드
EXCEPTION
END;

이러한 문법을 가지고 있는 트리거.

아래 예시 코드로 어떻게 쓰이는 지 확인.

기본 트리거 예시코드

---

아래 코드는 주말 오전 10시에서 오후 6시 사이 EMP 테이블에 DML 사용 못하게 하는 트리거이다.

CREATE OR REPLACE TRIGGER emp_dml_YHY
BEFORE INSERT OR UPDATE OR DELETE ON emp
 BEGIN
    IF((to_char(sysdate, 'DY')in ('토', '일'))AND
      (to_number(to_char(sysdate,'HH24')))>= 10 AND
      (to_number(to_char(sysdate,'HH24')))<=18) THEN
    RAISE_APPLICATION_ERROR(-20001,'주말변경불가합니다.'); /*결과값으로 출력되는 항목*/
    END IF;
    END;

이 트리거가 있으면

UPDATE emp SET sal = 0;

이라는 명령어를 주말 오전 10시에서 오후 6시 사이 실행시 오류가 나타나게 된다.

아래 코드는 명령문 실행 시 출력문으로 어떤 명령이 실행 된 것인지 알려주는 트리거이다.

CREATE OR REPLACE TRIGGER YHY_EMP
BEFORE INSERT OR UPDATE OF ename, sal OR DELETE ON emp/*emp 테이블에 있는 ename 과 sal칼럼*/
 BEGIN
  CASE
  WHEN INSERTING THEN
  		DBMS_OUTPUT.PUT_LINE('Inserting 중입니다.');
  WHEN UPDATING('sal') THEN
  		DBMS_OUTPUT.PUT_LINE('Updating sal 중입니다...');
  WHEN UPDATING('ename') THEN
  		DBMS_OUTPUT.PUT_LINE('Updating ename 중입니다...');
  WHEN DELETING THEN
  		DBMS_OUTPUT.PUT_LINE('Deleting 중입니다...');
  END CASE;
END;

이후 쿼리문으로 이런것들을 입력하면 결과값들이 다음과 같이 나온다.

INSERT INTO EMP (empno, ename) values (88, '88길동'); /*Inserting...*/
DELETE FROM EMP WHERE empno=88; /*Deleting...*/
UPDATE emp set sal = sal + 10 where empno = 7369; /*Updating sal...*/
UPDATE emp set ename = '오자바커' where empno = 7369 /*Updating ename...*/

FOR EACH LOW 절 예시

---

CREATE TABLE emp_yhy(
    old_sal number,
    new_sal number,
    u_date date,
    action varchar2(20)
);

위 쿼리로 테이블을 하나 생성해준다.

그리고 트리거를 하나 생성한다.

CREATE OR REPLACE TRIGGER tr_emp_update
 AFTER UPDATE OF sal on emp
   FOR EACH ROW
 BEGIN
INSERT INTO emp_yhy values(:old sal, :new sal, sysdate, 'UPDATE');
  END;

위 트리거는 EMP 테이블에 있는 sal 칼럼에 업데이트가 발생 했을 때 모든 행에 대해 old sal, new sal 을 갱신해주는 쿼리이다.

해당 트리거가 잘 작동하는 지 보기 위해 아래 쿼리르 실행해본다.

UPDATE emp SET sal = sal + 500; /*emp 테이블의 sal 칼럼을 기존보다 500씩 올리는 코드*/

이렇게 실행하면 데이터는 이런식으로 출력된다.

OLD_SALNEW_SALU_DATEACTION

800	1300	15/02/28	UPDATE
1600	2100	15/02/28	UPDATE
7777	8277	15/02/28	UPDATE
9998	10498	15/02/28	UPDATE

이런식으로 모든 열에 대해 실행된 것을 확인할 수 있다.

WHEN 절을 이용하여 조건이 맞는 경우에만 트리거가 동작

---

WHEN 절을 이용하여 트리거를 만들어보는 코드이다. 아래 코드.

CREATE OR REPLACE TRIGGER print_emp
BEFORE EACH ROW
  WHEN (new.sal>500) /*여기에 조건을 걸어준다.*/
DECLARE
	sal_diff number; /*아래 부분에서 사용할 변수 선언해주는 부분*/
 BEGIN
 	sal_diff := :new.sal - :old.sal;
 	dbms_output.put('OLD SALARY : ' || :old.sal);/*||문법과 : 붙이는 이유가 뭘까?*/
 	dbms_output.put(', NEW SALARY :' || :new.sal);
 	dbms_output.put_line(',급여차이' || sal_diff);
 END;