[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;
/