ORACLE 14. OBJECT(TRIGGER)

1.   < 트리거 TRIGGER >

   내가 지정한 테이블에 INSERT,UPDATE,DELETE등의 DML문에 의해 변경사항이 생길때(즉, 테이블에 이벤트 발생했을때)  매번 "자동으로 실행시킬 내용"을 미리 정의해둘 수 있는 객체 
    
    EX)
   - 회원 탈퇴시 기존의 회원테이블에 데이터 DELETE 후 곧바로 탈퇴한 회원들만 따로 보관하는 테이블에 
    자동으로 INSERT처리해야할때 
   - 회원의 신고횟수가 일정 수를 넘었을 경우 묵시적으로 해당 해원을 블랙리스트로 처리 되게끔할때 
   - 입출고에 대한 데이터가 기록(INSERT)될 때마다 해당 상품에 대한 재고수량을 매번 수정(UPDATE)해야될때
    
  

1.1.   * 트리거 종류 

    - SQL문의 실행시기에 따른 분류 
    > BEFORE TRIGGER : 내가 지정한 테이블에 이벤트가 발생되기 전에 트리거 실행 
    > AFTER TRIGGER : 내가 지정한 테이블에 이벤트가 발생된 후 트리거 실행 
   

  - SQL문에 의해 영향을 받는 각 행에 따른 분류 
> STATEMENT TRIGGER (문장트리거) : 이벤트가 발생한 SQL문에 대해 딱 한번만 트리거 실행
    

> ROW TRIGGER (행트리거) : 해당 SQL문이 실행될 때마다 매번 트리거 실행 
         (FOR EACH ROW옵션 기술해야됨)                            
   - :OLD - BEFORE UPDATE (수정전 데이터), BEFORE DELETE (삭제 전 데이터)
   - :NEW - AFTER INSERT (추가된 데이터), AFTER UPDATE (수정 후 데이터)
                             
     

1.2. * 트리거 생성 구문

     [표현법]
     'CREATE [OR REPLACE] TRIGGER 트리거명
     BEFORE | AFTER INSERT|UPDATE|DELETE ON 테이블명 
     [FOR EACH ROW]
     DECLARE 
        변수선언;
     BEGIN
        실행내용(위에 지정된 이벤트 발생시 묵시적으로 (자동으로) 실행시킬 구문 
     EXCEPITON 
        예외처리 구문;
     END;
     /

 

예시) EMPLOYEE테이블에 새로운행이 INSERT될때마다 자동으로 메시지 출력시켜주는 트리거 정의

CREATE OR REPLACE TRIGGER TRG_01
AFTER INSERT ON EMPLOYEE
BEGIN
    DBMS_OUTPUT.PUT_LINE('신입사원님 환영합니다!');
END;
/SQL

   예시) 상품 입고 및 출고 관련 예시 

--1. 상품에 대한 데이터 보관할 테이블 (TB_PRODUCT2)
DROP TABLE TB_PRODUCT;
CREATE TABLE TB_PRODUCT2(
    PCODE VARCHAR2(8) PRIMARY KEY,
    PNAME VARCHAR2(30) NOT NULL,
    BRAND VARCHAR2(30) NOT NULL,
    PRICE NUMBER,
    STOCK NUMBER DEFAULT 0
);
--상품 코드용 시퀀스 (SEQ_PCODE)
CREATE SEQUENCE SEQ_PCODE
NOCACHE;

--샘플데이터 3개 추가 
INSERT INTO TB_PRODUCT2 VALUES('PRO_'||LPAD(SEQ_PCODE.NEXTVAL,3,'0'),'갤럭시플립','삼성',1400000,DEFAULT);
INSERT INTO TB_PRODUCT2 VALUES('PRO_'||LPAD(SEQ_PCODE.NEXTVAL,3,'0'),'아이폰14PRO','애플',1500000,10);
INSERT INTO TB_PRODUCT2 VALUES('PRO_'||LPAD(SEQ_PCODE.NEXTVAL,3,'0'),'대륙폰','샤오미',700000,20);

--2. 상품 입출고 상세이력 테이블 (TB_PRODETAIL)
--어떤 상품이 어떤날짜에 몇개가 입고 또는 출고가 되었는지에 대한 데이터를 기록하는 테이블 
CREATE TABLE TB_PRODETAIL(
    DCODE NUMBER PRIMARY KEY,
    PCODE VARCHAR2(8) REFERENCES TB_PRODUCT2,
    PDATE DATE NOT NULL,
    AMOUNT NUMBER NOT NULL,
    STATUS CHAR(6) CHECK(STATUS IN ('입고','출고'))
);

--이력 번호용 시퀀스 
CREATE SEQUENCE SEQ_DCODE
NOCACHE;SQL

>> TB_PRODETAIL 테이블에서 INSERT이벤트 발생시 TB_PRODUCT2 테이블에 매번 자동으로
  재고수량 UPDATE 되게끔 트리거 정의 

CREATE OR REPLACE TRIGGER TRG_02
AFTER INSERT ON TB_PODETAIL
FOR EACH ROW
BEGIN 
	IF :NEW.STATUS = '입고'
      THEN
        UPDATE TB_PRODUCT2
           SET STOCK = STOCK + :NEW.AMOUNT
         WHERE PCODE = :NEW.PCODE;
    END IF;    
    IF :NEW.STAUE = '출고'
       THEN
         UPDATE TB_PRODUCT2
            SET STOCK = STOCK - :NEW.AMOUNT
          WHERE PCODE = :NEW.PCODE;
    END IF;      
END;
/SQL