[DB] TRIGGER

✍TRIGGER(트리거)

:  테이블이나 뷰가 INSERT, UPDATE, DELETE 등의 DML문에 의해 변경될 경우(테이블 이벤트 발생 시)
      자동으로(묵시적으로) 실행될 내용을 정의하여 저장하는 객체(PROCEDURE)

---

1. 트리거 종류

 

1) SQL문의 실행 시기에 따른 분류

• BEFORE TRIGGER : SQL문 실행 전 트리거 실행
• AFTER TRIGGER  : SQL문 실행 후 트리거 실행
          

2) SQL문의 의해 영향을 받는 각 ROW에 따른 분류

• ROW TRIGGER : SQL문 각 ROW에 대해 한번씩 실행
                        트리거 생성 시 FOR EACH ROW 옵션 작성 
  - FOR EACH ROW 안에 아래 두개를 작성할 수 있는데 
     :OLD : 참조 전 열의 값 (INSERT: 입력 전 자료, UPDATE : 수정 전 자료, DELETE : 삭제할 자료)
     :NEW   : 참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정 할 자료)

 

• STATMENT TRIGGER : SQL문에 대해 한번만 실행(DEFAULT TRIGGER)

 

참고 - 문장 트리거, 행 트리거

참고 1) 문장 트리거

트리거가 설정된 테이블에 트리거 이벤트가 발생하면 많은 행에 대해 변경 작업이 발생하더라도 오직 한번만 트리거를 발생시키는 방법
  Insert, Update, Delete 한 번만 실행 된다. 컬럼값이 변화가 생길 때마다 스스로 알아서 실행 된다.
  (FOR EACH ROW 옵션이 사용되지 않음)
  예를 들어 "UPDETE emp SET 급여 = 급여 * 1.1;" 문장이 실행되면 여러 행에 대하여 자료가 변경 되더라도 한번만 트리거가 실행된다.

 

참고2) 행 트리거
   조건을 만족하는 여러 개의 행에 대해 트리거를 반복적으로 여러 번 수행하는 방법으로 [FOR EACH ROW WHEN 조건]절 정의된다.
  컬럼의 데이터 행이 변화가 오면 실행된다. 변경 후의 행은 OLD, NEW 사용하여 가저올 수 있다. 
  (FOR EACH ROW 옵션이 됨)

---

1. TRIGGER 생성 방법

[표현식] 

 CREATE [OR REPLACE] TRIGGER 트리거명
    BEFORE | AFTER 
    INSERT | UPDATE | DELETE
    ON 테이블명
    [FOR EACH ROW] -- ROW TRIGGER 옵션
    [WHEN  조건]

    DECLARE  -- 선언부
    BEGIN -- 실행부
    [EXCEPTION]
    END;
    /

📝 예제

👉 PRO_DATAIL 테이블에 데이터 INSERT할때마다 

PRODUCT 테이블의 STOCK 컬럼 값을 자동으로 UPDATE하는 TRIGGER 구문 만들기 (입고, 출고) + 상품번호를 위한 SEQUENCE 필요

 

PRODUCT : 상품 정보 테이블 생성 / PRO_DATAIL : 상품 입출고 내역 테이블 생성

-- 상품 정보 테이블 생성
CREATE TABLE PRODUCT( 
    PCODE NUMBER PRIMARY KEY,       --상품코드
    PNAME VARCHAR2(200) NOT NULL,   --상품명 ,꼭 이름이 들어가게 NOT  NULL제약조건
    BRAND VARCHAR2(60) NOT NULL,    -- 제조사 브랜드명
    PRICE NUMBER NOT NULL,          --가격
    STOCK NUMBER DEFAULT 0          -- 재고, 기본은 0부터 시작 
);
--> Table PRODUCT이(가) 생성되었습니다.

-- 상품 입출고 내역 테이블
CREATE TABLE PRO_DETAIL(
    DCODE NUMBER PRIMARY KEY,       -- 각 행을 구분하는 식별자 역할 번호
    PDATE DATE DEFAULT SYSDATE,     -- 상품 입/출고일
    AMOUNT NUMBER NOT NULL,         -- 입/출고 개수
    STATUS CHAR(6) CHECK(STATUS IN('입고', '출고') ), --입/출고 상태
    -- 체크 제약조건 , 6바이트는 딱 두글자만 들어갈수 있고 STATUS에는 입고, 출고만 있을 수있다.
    
    PCODE NUMBER REFERENCES PRODUCT -- 상품 코드( 상품 정보 테이블과 관계형성)
    -- 두 테이블이 서로 관계가 있으므로 REFERENCES
);

 

-- 시퀀스 생성 //순차적 번호 자동 발생기 역할하는 객체
CREATE SEQUENCE SEQ_PCODE; -- 상품번호 생성 시퀀스
CREATE SEQUENCE SEQ_DCODE; -- 상품 입출고내역 구분 번호 생성 시퀀스

CREATE OR REPLACE TRIGGER STOCK_TRG
AFTER INSERT  ON PRO_DETAIL -- PRO_DETAIL 테이블에 INSERT 된 후 
FOR EACH ROW -- ROW TRIIGGER 옵션 : 행마다 TRIGGER를 적용한다.
BEGIN
    IF (:NEW.STATUS = '입고')
    THEN  -- 입고
        --PRODUCT 테이블에서 PCODE(상품번호)가 같은 행의 값을 증가
        UPDATE PRODUCT SET                        --1. 테이블 업데이트 진행하는데
        STOCK = STOCK + :NEW.AMOUNT   --3. 기존 재고에 새로 입고된 물량을 추가 
        WHERE PCODE = :NEW.PCODE;        --2. 만약 :NEW.PCODE와 PCODE가 같다면
    ELSE -- 출고
        UPDATE PRODUCT SET                       --1. 테이블 업데이트 진행하는데
        STOCK = STOCK - :NEW.AMOUNT   --3. 기존 재고에 새로 입고된 물량을 감소
        WHERE PCODE = :NEW.PCODE;       --2. 만약 :NEW.PCODE와 PCODE가 같다면
    END IF;
END;
/

👉  :NEW
: 새로 INSERT된 행을 가리키는 바인드변수
참조 후 열의 값 (INSERT : 입력 할 자료, UPDATE : 수정 할 자료)

---

입고 & 출고 실행

1) 입고
--PCODE가 1인 상품 100개 입고
INSERT INTO PRO_DETAIL  --입·출고니깐 DETAIL 테이블
VALUES( SEQ_DCODE.NEXTVAL, DEFAULT, 100, '입고', 1 );
-- SEQ_PCODE; -- 상품번호 생성 시퀀스, 출고일은 기본값인 오늘, 재고, 상태, 상품번호

INSERT INTO PRO_DETAIL  --1번 1100개
VALUES( SEQ_DCODE.NEXTVAL, DEFAULT, 1000, '입고', 1 );

--> 수행 후 PRO_DETAIL, PRODUCT 테이블 확인
SELECT * FROM PRO_DETAIL; -- 창고관리자 보는 테이블
SELECT * FROM PRODUCT; -- 판매자가 보는 테이블  (재고 입고 확인 테이블)
--> 이런식으로 용도에 맞게 테이블 구분할 수 있다. 

👉 .NEXTVAL:다음 시퀀스 번호를 얻어온다

-- 출고
INSERT INTO PRO_DETAIL
VALUES( SEQ_DCODE.NEXTVAL, DEFAULT, 900, '출고', 1);

SELECT * FROM PRO_DETAIL;

➡ 여기서 PRO_DETAIL 같이 구성한 테이블은 기록이 다 남기 때문에 유튜브 시청기록이나 조회수같은 곳에서 사용할 수 있는 테이블
그런데..! 트리거는 지정된 동작을 알아서 수행하는데 테이블을 수정하거나 데이터 수정 시 원치않게 동작하는 경우들이 있기때문에 유지보수가 힘들다. 

 

응용) 재고가 -가 되는 경우 '재고보다 많이 출고할 수 없습니다' 문자 출력

조건을 ELSE -- 출고 뒤에 IF로 추가조건

CREATE OR REPLACE TRIGGER STOCK_TRG
AFTER INSERT  ON PRO_DETAIL -- PRO_DETAIL 테이블에 INSERT 된 후 
FOR EACH ROW -- ROW TRIIGGER 옵션 : 행마다 TRIGGER를 적용한다.
BEGIN
    IF (:NEW.STATUS = '입고')
    THEN  -- 입고
        --PRODUCT 테이블에서 PCODE(상품번호)가 같은 행의 값을 증가
        UPDATE PRODUCT SET                        --1. 테이블 업데이트 진행하는데
        STOCK = STOCK + :NEW.AMOUNT   --3. 기존 재고에 새로 입고된 물량을 추가 
        WHERE PCODE = :NEW.PCODE;        --2. 만약 :NEW.PCODE와 PCODE가 같다면

    ELSE -- 출고
        IF ( (SELECT STOCK
                FROM PRODUCT
                WHERE PCODE = :NEW.PCODE) - :NEW.AMOUNT >=  0) -- 기존 재고 - 새로 입고한 재고가  0이상이라면
       THEN 
                UPDATE PRODUCT SET                       --1. 테이블 업데이트 진행하는데
                STOCK = STOCK - :NEW.AMOUNT   --3. 기존 재고에 새로 입고된 물량을 감소
                WHERE PCODE = :NEW.PCODE;        --2. 만약 :NEW.PCODE와 PCODE가 같다면
        ELSE
            DBMS_OUTPUT.PUT_LINE('재고보다 많이 출고할 수 없습니다');
    END IF;
END;
/