[SQL]-TRIGGER

1. TRIGGER

💡 SQL에서 트리거는 특정 이벤트가 발생할 때 자동으로 실행되는 특별한 저장 프로시저이다. 이 포스팅에서는 트리거를 활용하여 주문 테이블과 상품 입출고를 관리하는 예제를 정리한다.

 

1-1. 트리거 정의하기

1-1-1. DELIMITER

트리거를 정의할 때는 문장의 끝을 지정하는 DELIMITER를 변경한다.

DELIMITER //  -- 문장의 끝을 지정하는 "//" 문자로 지정.

트리거는 프로시저를 활용하여 이벤트를 처리한다.

예를 들어, 주문 메뉴 테이블(tbl_order_menu)에 주문이 들어올 때마다 주문 테이블(tbl_order)의 총 금액을 업데이트하는 트리거를 작성할 수 있다.

1-1-2. CREATE TRIGGER

사진과 같은 경우에 triiger를 사용한다.

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

1-1-3. DELIMITER  재설정(;)

트리거를 작성한 후 다시 기본 DELIMITER인 세미콜론(;)으로 돌아간다.

DELIMITER ;

1-1-4. TRIGGER 확인

SHOW TRIGGERS;

 

1-2. 주문 테이블에 데이터 삽입

먼저 주문 테이블(tbl_order)에 데이터를 삽입한다.

INSERT INTO tbl_order (order_code, order_date, order_time, total_order_price)
VALUES (
    NULL,
    CONCAT(CAST(YEAR(NOW()) AS CHAR(4)), CAST(LPAD(MONTH(NOW()), 2, '0') AS CHAR(2)), CAST(LPAD(DAYOFMONTH(NOW()), 2, '0') AS CHAR(2))),
    CONCAT(CAST(LPAD(HOUR(NOW()), 2, '0') AS CHAR(2)), CAST(LPAD(MINUTE(NOW()), 2, '0') AS CHAR(2)), CAST(LPAD(SECOND(NOW()), 2, '0') AS CHAR(2))),
    0
);

1-2-1. TRIGGER 작동 확인

INSERT INTO tbl_order_menu (order_code, menu_code, order_amount) VALUES (1, 2, 3);
INSERT INTO tbl_order_menu (order_code, menu_code, order_amount) VALUES (1, 6, 2);

SELECT * FROM tbl_order;

 

1-3. 상품 입출고와 관련된 트리거 활용 예제

1-3-1. 이력 테이블 생성

CREATE TABLE product (
    pcode INT PRIMARY KEY AUTO_INCREMENT,
    pname VARCHAR(30),
    brand VARCHAR(30),
    price INT,
    stock INT DEFAULT 0,
    CHECK (stock >= 0)
);

1-3-2. 내역 테이블 생성

CREATE TABLE pro_detail (
    dcode INT PRIMARY KEY AUTO_INCREMENT,
    pcode INT,
    pdate DATE,
    amount INT,
    STATUS VARCHAR(10) CHECK (STATUS IN ('입고', '출고')),
    FOREIGN KEY (pcode) REFERENCES product(pcode)
);

1-3-3. 트리거 작성

상품 입출고 시마다 재고를 업데이트하는 트리거를 작성한다.

DELIMITER //

CREATE OR REPLACE TRIGGER trg_product_after
    AFTER INSERT
    ON pro_detail
    FOR EACH ROW
BEGIN
    IF NEW.status = '입고' THEN
        UPDATE product
        SET stock = stock + NEW.amount
        WHERE pcode = NEW.pcode;
    ELSEIF NEW.status = '출고' THEN
        UPDATE product
        SET stock = stock - NEW.amount
        WHERE pcode = NEW.pcode;
    END IF;
END//

DELIMITER ;

 

1-3-4. 트리거 확인

트리거가 제대로 작성되었는지 확인하기 위해 다음 명령어를 사용한다.

SHOW TRIGGERS;

1-3-5. 상품 데이터 삽입 및 트리거 작동 확인

먼저 상품 테이블에 데이터를 삽입한다.

INSERT INTO product (pname, brand, price, stock) VALUES ('갤럭시플릿', '삼송', 900000, 5);
INSERT INTO product (pname, brand, price, stock) VALUES ('아이펀15', '아이뽄', 1100000, 5);
INSERT INTO product (pname, brand, price, stock) VALUES ('투명폰', '삼송', 2100000, 5);

 

다음으로 입출고 내역을 삽입하여 트리거가 작동하는지 확인한다.

INSERT INTO pro_detail (pcode, pdate, amount, STATUS) VALUES (3, CURDATE(), 5, '입고');
INSERT INTO pro_detail (pcode, pdate, amount, STATUS) VALUES (2, CURDATE(), 3, '출고');

 

트리거가 작동하여 재고가 올바르게 업데이트 되었는지 확인한다.

SELECT * FROM product;

 

<결과>

이를 통해 트리거가 상품 입출고 시마다 재고를 자동으로 업데이트하는 것을 확인할 수 있다.