SQL - TRIGGER

TRIGGER

• TABLE에 INSERT/UPDATE/DELETE(DML) 구문에 의해 변경될 경우 자동으로 실행될 내용을 정의해 놓는 객체
  • TABLE에 이벤트 발생 시
• SQL문의 실행 시기에 따라 분류
  • BEFORE TRIGGER : 해당 SQL 문장 실행 전 TRIGGER 실행
  • AFTER TRIGGER : 실행 후
• SQL문에 의해 영향을 받는 행에 따른 분류
  • 문장 TRIGGER(STATEMENT TRIGGER) : 해당 SQL문에 대해 한 번만 TRIGGER 실행
  • 행 TRIGGER(ROW TRIGGER) : 해당 SQL문에 영향을 받는 행마다 TRIGGER 샐행
    • FOR EACH ROW 옵션 기술

CREATE OR REPLACE TRIGGER 트리거명
BEFORE/AFTER INSERT/UPDATE/DELETE ON 테이블명
[FOR EACH ROW]
[DECLARE
    선언부]
BEGIN
    실행부
[EXECPTION
    예외부]
END;
/

-- [] : 생략 가능

TRIGGER 생성

CREATE OR REPLACE TRIGGER TRG_01
AFTER UPDATE ON EMPLOYEE
BEGIN
    DBMS_OUTPUT.PUT_LINE('업데이트 실행');
END;
/

트리거 생성 후 확인도 해봤다. 잘 생성이 되었다.

UPDATE EMPLOYEE
SET DEPT_CODE = 'D1'
WHERE DEPT_CODE =  'D9';

EMPLOYEE 테이블 내에 DEPT_CODE가 ‘D9’인 행을 모두 ‘D1’으로 변경했다.

CREATE OR REPLACE TRIGGER TRG_02
AFTER UPDATE ON EMPLOYEE
FOR EACH ROW -- 행 트리거
BEGIN
    DBMS_OUTPUT.PUT_LINE('변경 전 : ' || :OLD.DEPT_CODE ||', 변경 후 : ' || :NEW.DEPT_CODE);
END;
/

이번에는 FOR EACH ROW를 추가하여 행 트리거를 생성했다.
위에 TRG_01은 문장 트리거이다.

EMPLOYEE 테이블의 행을 변경했을 때 TRI_01 / TRI_02 트리거가 각각 다르게 출력되는 것을 확인했다.
TRI_01 : 문장 트리거이기 때문에 5개의 행이 변경 되었어도 한 번만 실행된다.
TRI_02 : 행 트리거이기 때문에 5개의 행이 변경 되면 영향 받는 행의 갯수만큼(예제에서는 5번) 실행된다.

---

실습 예제

오늘 실습은 재고 관리 현황을 예로 해서 진행했다.
역시 상황 예시가 주어지고 실습을 하면 이해가 더 빠른 거 같다.

1) 상품 테이블 생성 PRODUCT

CREATE TABLE PRODUCT(
    PCODE NUMBER PRIMARY KEY, -- 상품 코드
    PNAME VARCHAR2(30),       -- 상품명
    BRAND VARCHAR2(30),       -- 브랜드명
    PRICE NUMBER,             -- 가격
    STOCK NUMBER DEFAULT 0    -- 재고
);

2) 상품코드 중복 방지 객체(SEQUENCE) 생성

CREATE SEQUENCE SEQ_PCODE;

3) 상품 데이터 INSERT

INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, 'Z플립', '삼성', 1500000, DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '아이폰13', '애플', 1000000, DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '샤오미폰', '샤오미', 800000, DEFAULT);

4) 입출고 상세 이력 테이블 PRODETAIL & 시퀀스 생성

CREATE TABLE PRODETAIL(
    DCODE NUMBER PRIMARY KEY,  -- 입출력 이력 코드
    PDATE DATE,                -- 상품 입출고일
    AMOUNT NUMBER,             -- 수량
    STATUS VARCHAR2(10),       -- 상태 > 입출고
    PCODE NUMBER,              -- 상품 코드(외래키 지정, PRODUCT 테이블 참조)
    CHECK(STATUS IN ('입고', '출고')),
    FOREIGN KEY(PCODE) REFERENCES PRODUCT

    -- STATUS 컬럼 : 입고 OR 출고만 저장. CHECK 제약 조건 추가
);

CREATE SEQUENCE SEQ_DCODE;

5) 수업 시간 실습 예제

-- 1번 상품이 오늘 날짜로 10개 입고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 10, '입고', 1);

• PRODETAIL에 10개를 추가해도 PRODUCT 테이블에는 반영되지 않는다. 당연.
  PRODUCT 테이블에는 추가하지 않았기 때문에!

UPDATE PRODUCT
SET STOCK = STOCK + 10
WHERE PCODE = 1;

• 그래서 PRODUCT 테이블에도 1번 상품의 재고를 10개로 수정해줬다.

• 두 개의 테이블의 정보가 연결되어 있지만, 재고가 들어왔을 때 두 개의 테이블을 모두 수정해줘야하는 일이 발생했다.
  테이블이 별로 없을 때는 가능하겠지만 그런 일은 극히 드물 것이고(실무에서) 가능하더라도 실수가 생길 수 있다.
  이럴 때 TRIGGER를 사용하면 된다.

재고 테이블(PRODETAIL)에 데이터 삽입 시 상품 테이블(PRODUCT)에 재고 수량을 업데이트하는 TRIGGER 생성

CREATE OR REPLACE TRIGGER TRG_PRO_STOCK
AFTER INSERT ON PRODETAIL
FOR EACH ROW -- 행 트리거 사용
BEGIN
    DBMS_OUTPUT.PUT_LINE(:NEW.STATUS || ' ' || :NEW.AMOUNT || ' ' || :NEW.PCODE);
END;
/

트리거 생성 후 테이블을 수정했을 때 재고 테이블(PRODETAIL)을 수정하면
위와 같은 화면이 나온다.
차례대로 STATUS(상태) / AMOUNT(수량) / PCODE(상품코드)이다. 이걸 이용해서 입고, 출고가 되었을 때 각각 업데이트문을 실행시켜 준다.

CREATE OR REPLACE TRIGGER TRG_PRO_STOCK
AFTER INSERT ON PRODETAIL
FOR EACH ROW
BEGIN
    -- 상태 / 수량 / 상품코드
    DBMS_OUTPUT.PUT_LINE(:NEW.STATUS || ' ' || :NEW.AMOUNT || ' ' || :NEW.PCODE);
    
    -- 상품 입고
    IF (:NEW.STATUS = '입고') THEN
        UPDATE PRODUCT
        SET STOCK = STOCK + :NEW.AMOUNT
        WHERE PCODE = :NEW.PCODE;
    END IF;
    
    -- 상품 출고
    IF (:NEW.STATUS = '출고') THEN
        UPDATE PRODUCT
        SET STOCK = STOCK - :NEW.AMOUNT
        WHERE PCODE = :NEW.PCODE;
    END IF;
END;
/

와.. 이건 다시 봐도.. 트리거와 IF문.. 업데이트문의 조합. 웬욜.

-- 2번 상품이 오늘 날짜로 20개 입고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 20, '입고', 2);

트리거 생성 후 다시 상품을 입고시켰다.

결과 : PRODETAIL 테이블의 수정이 발생 하면서 트리거가 실행 되었다.
트리거 실행 시, STATUS의 컬럼값이 ‘입고’일 경우 수량을 추가시켰기 때문에
PRODUCT(상품) 테이블의 내용도 출력된 것을 확인했다.

---

재밌고 신기하고 어렵다…

---