[Oracle] Trigger 사용하기

트리거(Trigger)

- 특정 이벤트가 발생하면 다른 테이블이 자동으로 변경되도록 하는 일종의 프로시져
- 트리거의 종류는 문장 단위 트리거와 행단위 트리거로 구분

 

기본형태

(사용형식)
CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE|AFTER INSERT|UPDATE|DELETE -- 이벤트
ON 테이블명
[FOR EACH ROW]
[WHEN 조건]
[DECLARE]
선언부
BEGIN
실행부(트리거 본문)
END;

(사용형식)
CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE|AFTER    INSERT|UPDATE|DELETE -- 이벤트
ON 테이블명
[FOR EACH ROW]
[WHEN 조건]
[DECLARE]
선언부
BEGIN
실행부(트리거 본문)
END;

• BEFORE|AFTER : 트리거 본문(BEGIN ~ END 사이의 명령문)와 수행시점
  ☞ (BEFORE)  ex. 삭제되기 전에 퇴직자 테이블에 가져다놓고 삭제
  ☞ 대부분 AFTER 사용
  ☞ BEFORE|AFTER 트리거 타이밍, 하나만 사용
• INSERT|UPDATE|DELETE : 트리거를 유발하는 이벤트로 'OR' 연산자를 사용할 수 있다.
  ☞ BEFORE|AFTER 이벤트 발생 (DML 명령)-> BEGIN~END 트리거본문에서 수행
  ☞ OR 연산자만 사용 가능! (AND는 X)
  ☞ 의사레코드를 이용하여 이벤트에서 발생되어진 테이블의 내용을 트리거본문에서 사용가능
• FOR EACH ROW : 트리거 유형을 결정. 생략하면 문장단위 트리거가 됨
  - 문장단위 트리거 : 이벤트가 발생될 때 단 한번만 트리거 수행
  - 행  단위 트리거 : 이벤트의 결과가 여러 행으로 구성된 경우 각 행마다 트리거 수행
  ☞ 대부분의 트리거가 FOR EACH ROW (행 단위 트리거)
• WHEN 조건 : 행 단위 트리거에서만 사용 가능하며 트리거 수행에 좀더 구체적인 조건을 부여할 때 사용
  ☞ 하나의 트리거가 완성되어지기 전에 또다른 트리거를 호출하는 경우
  ☞ immutable (해당 테이블에 누구도 접근할 수 없도록 막음)
  ☞ 트리거 발생을 구체적으로 제한
• 트리거 본문에는 DCL(Data Control Language : COMMIT, ROLLBACK 등)을 사용할 수 없다.

 

 

---

사용예제

- 문장단위 트리거

사용예) 분류 테이블에서 분류번호가(LPROD_GU)가 'P501' 이후 자료를 삭제한 후 '자료가 삭제되었음'을 출력하는
트리거를 작성하시오
-- 문장단위 트리거


CREATE OR REPLACE TRIGGER TG_DELETE_LPROD
AFTER DELETE ON LPROD
BEGIN
DBMS_OUTPUT.PUT_LINE('자료가 삭제되었음');
END;

DELETE FROM LPROD WHERE LPROD_GU>='P501';
SELECT * FROM LPROD;
-- 트리거 내용은 DBMS 출력창에 출력
-- DELETE를 해도 COMMIT을 해야 하드디스크에서도 자료가 삭제됨


 사원 테이블에서 사원번호, 사원명, 입사일, 부서번호, 직무코드를 조회하여 퇴직자 테이블(RETIRE)을 생성하시오
(서브쿼리를 이용한 테이블 생성)
CREATE TABLE 테이블명 AS 서브쿼리;


CREATE TABLE RETIRE AS
        SELECT EMPLOYEE_ID,EMP_NAME,DEPARTMENT_ID,JOB_ID
          FROM EMPLOYEES;

DELETE FROM RETIRE;
COMMIT;

 

 

- * 사원 테이블에서 사원번호 109번~113번 사원을 퇴직처리 하시오.
퇴직자는 사원테이블에서 삭제하되 삭제전 자료를 퇴직자 테이블에 삽입하시오

CREATE OR REPLACE TRIGGER TG_DEL_EMP
    BEFORE DELETE ON EMPLOYEES
    FOR EACH ROW
BEGIN
    INSERT INTO RETIRE
        VALUES(:OLD.EMPLOYEE_ID,:OLD.EMP_NAME,:OLD.DEPARTMENT_ID,:OLD.JOB_ID,SYSDATE);
END;
--  DECLARE는 사용하는 변수가 있을 때만 사용 (없으면 생략)
-- :OLD.EMPLOYEE_ID : 삭제될 위치

DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID BETWEEN 109 AND 113;


COMMIT;
ROLLBACK;

 

 

재고수불 트리거

* 오늘이 2020년 6월 11일이라고 가정하고 다음 자료를 매입했을때 후속 동작을 트리거로 작성하시오
[매입자료]
날짜 : 2020/06/11
---------------------------------
상품코드 수량 단가
---------------------------------
P102000005 12 1990000
P202000007 20 25000
P201000021 7 230000
P201000009 15 33000
---------------------------------

CREATE OR REPLACE TRIGGER TG_BUYPROD_CHANGE
    AFTER INSERT OR UPDATE OR DELETE ON BUYPROD
    FOR EACH ROW
DECLARE
    V_QTY NUMBER:=0;
    V_PROD PROD.PROD_ID%TYPE;
BEGIN
    IF INSERTING THEN
        V_QTY:=(:NEW.BUY_QTY);
        V_PROD:=(:NEW.BUY_PROD);
    ELSIF UPDATING THEN
        V_QTY:=(:NEW.BUY_QTY - :OLD.BUY_QTY);
        V_PROD:=(:NEW.BUY_PROD);
    ELSIF DELETING THEN
        V_QTY:=-(:OLD.BUY_QTY);
        V_PROD:=(:OLD.BUY_PROD);
    END IF;

    UPDATE REMAIN A
       SET A.REMAIN_I = A.REMAIN_I + V_QTY,
           A.REMAIN_J_99 = A.REMAIN_J_99 +  V_QTY,
           A.REMAIN_DATE = TO_DATE('20200611')
     WHERE A.REMAIN_YEAR = '2020'
       AND A.PROD_ID=V_PROD;
END;


[매입자료]
날짜 : 2020/06/11
---------------------------------
  상품코드      수량        단가
---------------------------------
P102000005      12      1990000
P202000007      20        25000
P201000021       7       230000
P201000009      15        33000
---------------------------------

INSERT INTO BUYPROD VALUES (TO_DATE('20200611'),'P102000005',12,1990000);
INSERT INTO BUYPROD VALUES (TO_DATE('20200611'),'P202000007',20,25000);
INSERT INTO BUYPROD VALUES (TO_DATE('20200611'),'P201000021',7,230000);
INSERT INTO BUYPROD VALUES (TO_DATE('20200611'),'P201000009',15,33000);

 

---

내가 만들어본 트리거 (도서대여 테이블에서 사용)

- 책 대여 테이블에 새로운 데이터 insert/update/delete 되면
도서 테이블에서 도서 가능 여부가 업데이트 되도록 함

CREATE OR REPLACE TRIGGER TG_BOOK_CHANGE
    AFTER INSERT OR UPDATE OR DELETE ON RENT
    FOR EACH ROW    
DECLARE
    V_BOOK_RENTABLE BOOK.BOOK_RENTABLE%TYPE;        
    V_RENT_BOOKNUM NUMBER:=0;
    V_RENT_NUM NUMBER:=0;        
BEGIN

    IF INSERTING THEN
        V_BOOK_RENTABLE:='N';       
        V_RENT_BOOKNUM:=(:NEW.BOOK_NUMBER);
        V_RENT_NUM:=(:NEW.RENT_NUM);      

    ELSIF DELETING THEN
        V_BOOK_RENTABLE:='Y';        
        V_RENT_BOOKNUM:=(:OLD.BOOK_NUMBER);            
        V_RENT_NUM:=(:OLD.RENT_NUM);
        
    END IF;

    UPDATE BOOK A
       SET A.BOOK_RENTABLE = V_BOOK_RENTABLE
     WHERE A.BOOK_NUMBER = V_RENT_BOOKNUM;
END;

 

- 대여 테이블에서 대여가 끝난 일자 상태가 업데이트 되면 
대여가능 상태가 Y가 되도록 함

CREATE OR REPLACE TRIGGER TG_BOOK_RENT_END
        AFTER UPDATE OF RENT_END ON RENT
        FOR EACH ROW    
    DECLARE       
        V_BOOK_RENTABLE BOOK.BOOK_RENTABLE%TYPE;        
        V_RENT_BOOKNUM NUMBER:=0;
        V_RENT_NUM NUMBER:=0; 

    BEGIN   
        IF UPDATING THEN
            V_BOOK_RENTABLE:='Y';       
            V_RENT_BOOKNUM:=(:NEW.BOOK_NUMBER);
            V_RENT_NUM:=(:NEW.RENT_NUM);              
        END IF;

        UPDATE BOOK A
           SET A.BOOK_RENTABLE = V_BOOK_RENTABLE
         WHERE A.BOOK_NUMBER = V_RENT_BOOKNUM;
    END;

 

CREATE OR REPLACE TRIGGER TG_BOOK_RESERVE_UPDATE --
        AFTER UPDATE OF BOOK_RESERVATION ON RENT
        FOR EACH ROW    
    DECLARE       
        V_BOOK_RENTABLE BOOK.BOOK_RENTABLE%TYPE;        
        V_RENT_BOOKNUM NUMBER:=0;
        V_RENT_NUM NUMBER:=0; 

    BEGIN   
        IF UPDATING THEN
            V_BOOK_RENTABLE:='N';       
            V_RENT_BOOKNUM:=(:NEW.BOOK_NUMBER);
            V_RENT_NUM:=(:NEW.RENT_NUM);              
        END IF;

        UPDATE BOOK A
           SET A.BOOK_RENTABLE = V_BOOK_RENTABLE
         WHERE A.BOOK_NUMBER = V_RENT_BOOKNUM;
    END;