1. TRIGGER

• 데이터베이스 테이블에서 발생하는 특정 이벤트(INSERT, UPDATE, DELETE)가 발생했을 때 자동으로 실행되는 데이터베이스 객체이다.

• 주요 사용 목적은 데이터의 무결성을 유지하고 복잡한 비즈니스 로직을 처리하기 위함이다.

• 다만 트리거를 남용할 시 성능 문제나 복잡성 증가와 같은 부정적인 영향을 줄 수 있다.

• 내역에 INSERT 하는 순간 이력에 UPDATE 되었으면 좋겠으니까 이걸 세팅해둔 것을 트리거라고 한다.

• 종류

  • BEFORETRIGGER
    • 이벤트가 발생하기 전에 실행되며 데이터의 유효성 검사나 변형에 주로 사용
  • AFTERTRIGGER
    • 이벤트가 발생한 후에 실행되며 로깅, 알림 전송 등의 작업에 적합

• 문법

• delimiter를 통해 ;를 //로 바꿔준다.
• 참고

  • 이력: update 되는 테이블

  • 내력: insert 되는 테이블

    

1. TRIGGER 생성

• TRIGGER 내역과 이력
  • INSERT가 발생한 후에 UPDATE가 진행 된다.
  • NEW는 INSERT에서 나온 값이다.
• 주문 메뉴(tbl_order_menu) 테이블에 INSERT가 되고 나서 주문(tbl_order) 테이블의 총 합계가 UPDATE 될 수 있는 트리거 생성

-- 메뉴 추가 시 UPDATE 자동화 TRIGGER 만들기
DELIMITER //

CREATE OR REPLACE TRIGGER after_order_menu_insert
    AFTER INSERT
    ON tbl_order_menu
    FOR EACH ROW
BEGIN
    UPDATE tbl_order
    SET total_order_price = total_order_price + NEW.order_amount * (SELECT menu_price FROM tbl_menu WHERE menu_code = NEW.menu_code)
    WHERE order_code = NEW.order_code;
END//      

DELIMITER ;

2. TRIGGER 활용

• 주문 테이블과 주문 메뉴 테이블에 INSERT 작업 이후 주문 테이블의 총 합계가 UPDATE 되는 것 확인

-- 주문 테이블(tbl_order)에 insert 후 주문 메뉴 테이블(tbl_order_menu)에 주문한 메뉴마다 insert 후 주문 테이블에 총 금액이 업데이트 되는지 확인하자
-- 주문 테이블 INSERT
INSERT
  INTO tbl_order
(
  order_code
, order_date
, order_time
, total_order_price
)
VALUES
(
  NULL
, CONCAT(CAST(YEAR(NOW()) AS VARCHAR(4))
	    , CAST(LPAD(MONTH(NOW()), 2, 0) AS VARCHAR(2))
	    , CAST(LPAD(DAY(NOW()), 2, 0) AS VARCHAR(2)))
, CONCAT(CAST(LPAD(HOUR(NOW()), 2, 0) AS VARCHAR(2))
       , CAST(LPAD(MINUTE(NOW()), 2, 0) AS VARCHAR(2))
       , CAST(LPAD(SECOND(NOW()), 2, 0) AS VARCHAR(2)))
, 0			
);

-- 주문 메뉴 테이블 INSERT 1
INSERT
  INTO tbl_order_menu
(
  order_code
, menu_code
, order_amount
)
VALUES
(
  1
, 3
, 2
);

-- 주문 메뉴 테이블 INSERT 2
INSERT
  INTO tbl_order_menu
(
  order_code
, menu_code
, order_amount
)
VALUES
(
  1
, 6
, 3
);

SELECT * FROM tbl_order;
SELECT * FROM tbl_order_menu;

-- 다시 되돌려 테스트 해보고자 할 경우
-- 1) rollback하기
ROLLBACK;
-- 2) 기존 데이터 지우기
DELETE FROM tbl_order WHERE 1 = 1;
DELETE FROM tbl_order_menu WHERE 1 = 1;

-- 이후 AUTO_INCREMENT도 다시 초기화 해 준다.
ALTER TABLE tbl_order AUTO_INCREMENT = 1;

• 상품 입출과 관련 trigger 활용 예제

-- 상품 입출과 관련 trigger 활용 예제
DROP TABLE pro_detail;
DROP TABLE product;

CREATE TABLE PRODUCT(           -- 이력 테이블(UPDATE가 발생하는 테이블)
  PCODE INT PRIMARY KEY auto_increment,
  PNAME VARCHAR(30),
  BRAND VARCHAR(30),
  PRICE INT,
  STOCK INT DEFAULT 0,
  CHECK(STOCK >= 0)
);

CREATE TABLE PRO_DETAIL(        -- 내역 테이블(INSERT가 발생하는 테이블)
  DCODE INT PRIMARY KEY auto_increment,
  PCODE INT,
  PDATE DATE,
  AMOUNT INT,
  STATUS VARCHAR(10) CHECK(STATUS IN ('입고', '출고')),
  FOREIGN KEY(PCODE) REFERENCES PRODUCT  -- 어차피 해당 테이블의 PK컬럼에 대한 외래키이므로 PK컬럼은 적지 않아도 된다.(알아서 인식함)
);

INSERT
  INTO PRODUCT
(
  PCODE, PNAME, BRAND
, PRICE, STOCK
)
VALUES
(
  null, '갤럭시플립', '삼송'
, 900000, 5
);

INSERT
  INTO PRODUCT
(
  PCODE, PNAME, BRAND
, PRICE, STOCK
)
VALUES
(
  null, '아이펀 14', '애펄'
, 1000000, 5
);

INSERT
  INTO PRODUCT
(
  PCODE, PNAME, BRAND
, PRICE, STOCK
)
VALUES
(
  null, '투명폰 3', '헬쥐'
, 1000000, 5
);

SELECT * FROM PRODUCT;
SELECT * FROM PRO_DETAIL;

INSERT
  INTO PRO_DETAIL
(
  DCODE, PCODE, PDATE
, AMOUNT, STATUS
)
VALUES
(
  null, 1, CURDATE()
, 5, '입고'
);

INSERT
  INTO PRO_DETAIL
(
  DCODE, PCODE, PDATE
, AMOUNT, STATUS
)
VALUES
(
  null, 2, CURDATE()
, 2, '출고'
);

SELECT * FROM PRODUCT;
SELECT * FROM PRO_DETAIL;

-- -------------------------------------------------------------
DELIMITER //
CREATE OR REPLACE TRIGGER TRG_PRODUCTAFTER 
AFTER INSERT 
ON PRO_DETAIL
FOR EACH ROW
BEGIN
  IF NEW.STATUS = '입고' THEN
    UPDATE PRODUCT A
       SET A.STOCK = A.STOCK + NEW.AMOUNT
     WHERE A.PCODE = NEW.PCODE;
  ELSEIF NEW.STATUS = '출고' THEN
    UPDATE PRODUCT A 
       SET A.STOCK = A.STOCK - NEW.AMOUNT
     WHERE A.PCODE = NEW.PCODE;
  END IF;
END;
//
DELIMITER ;
-- if 만약에
-- elseif 만약을 제외한 나머지에 대한 조건
-- end if 끝