SQL 트리거 활용을 통한 자동화된 데이터 관리

데이터베이스 시스템에서 비즈니스 로직의 무결성을 보장하고 데이터 간의 정합성을 유지하기 위해 트리거 (Trigger) 를 효과적으로 적용하는 방법을 다룹니다. 본 내용에서는 주문明细(Lineitem) 테이블의 변경 사항이 주문(Order) 의 합계 금액과 부품 공급(PartSupp) 재고에 미치는 영향을 자동으로 처리하는 구현 사례를 제시합니다.

1. AFTER 트리거: 주문 합계 금액 동기화

주문 상세 항목 (Lineitem) 이 수정되거나 추가, 삭제될 때, 상위 주문 (Orders) 테이블의 총 가격 (TotalPrice) 을 실시간으로 갱신해야 합니다. 이를 위해 ALTERED EVENT가 발생한 후 동작하는 AFTER 트리기를 정의하여 데이터를 통합 관리합니다.

1.1 주문 내역 수정 시 합계 계산 업데이트

주문 세부 정보의 가격 관련 필드 (가격, 할인율, 세율) 가 변경되면 이전 값을 차감하고 새로운 계산된 가격을 더하여 주문 총액에 반영합니다.

DELIMITER $$
CREATE TRIGGER sync_order_total_on_update 
AFTER UPDATE ON lineitem
FOR EACH ROW 
BEGIN
    DECLARE price_delta DECIMAL(19, 2);
    
    -- 기존 항목 금액과 신규 항목 금액 차이 계산
    SET price_delta = (NEW.extendedprice * NEW.discount * (1.00 + NEW.tax)) 
                      - (OLD.extendedprice);
                      
    UPDATE orders 
    SET totalprice = totalprice + price_delta
    WHERE orderkey = NEW.orderkey;
END$$
DELIMITER ;

1.2 주문 내역 추가 시 합계 반영

새로운 주문 행이 삽입되면 해당 행의 계산된 금액을 부모 주문 레코드의 총 비용에 즉시 추가합니다.

DELIMITER $$
CREATE TRIGGER sync_order_total_on_insert 
AFTER INSERT ON lineitem
FOR EACH ROW 
BEGIN
    UPDATE orders 
    SET totalprice = totalprice + (NEW.extendedprice * NEW.discount * (1.00 + NEW.tax))
    WHERE orderkey = NEW.orderkey;
END$$
DELIMITER ;

1.3 주문 내역 삭제 시 합계 차감

특정 주문 행이 제거되면 해당 행의 원래 금액을订单 전체 합계에서 빼내어 잔여 금액의 정확도를 유지합니다.

DELIMITER $$
CREATE TRIGGER sync_order_total_on_delete 
AFTER DELETE ON lineitem
FOR EACH ROW 
BEGIN
    UPDATE orders 
    SET totalprice = totalprice - OLD.extendedprice
    WHERE orderkey = OLD.orderkey;
END$$
DELIMITER ;

1.4 기능 검증 테스트

정의된 트리거가 정상적으로 작동하는지 확인하기 위해 특정 키 (orderkey) 를 가진 레코드에 대해 연쇄 작업을 수행합니다.

# 초기 상태 확인
SELECT totalprice FROM orders WHERE orderkey = 1012;
SELECT linenumber, extendedprice FROM lineitem WHERE orderkey = 1012; 

# 수정 테스트
UPDATE lineitem SET extendedprice = 200000 WHERE orderkey = 1012 AND linenumber = 1;
SELECT totalprice FROM orders WHERE orderkey = 1012;

# 추가 테스트
INSERT INTO lineitem(orderkey, linenumber, extendedprice, discount, tax)  
VALUES (1013, 3, 100000, 0.85, 0.23);
SELECT totalprice FROM orders WHERE orderkey = 1013;

# 삭제 테스트
DELETE FROM lineitem WHERE orderkey = 1013 AND linenumber = 3;
SELECT totalprice FROM orders WHERE orderkey = 1013;

2. BEFORE 트리거: 재고 용량 사전 검증

데이터가 실제로 물리적으로 저장되기 전에 조건을 검사하여 유효하지 않은 상태를 방지하는 BEFORE 트리기 구조입니다. 특히 부품 공급 가능 수량을 확인하여 오버로우 (Over-write) 상황을 막습니다.

2.1 수량 수정 전 재고 충분성 확인

주문 수량을 변경하려는 경우, 현재 부품 공급 기록의 잔여량이 요청된 수량을 충족할 때까지 허용됩니다. 충족하지 못할 경우 예외 신호를 발생시켜 작업을 중단합니다.

DELIMITER $$
CREATE TRIGGER check_stock_before_update 
BEFORE UPDATE ON lineitem
FOR EACH ROW 
BEGIN
    DECLARE current_avail_quantity INT;
    
    SELECT availqty INTO current_avail_quantity 
    FROM partsupp 
    WHERE partkey = NEW.partkey AND suppkey = NEW.suppkey;

    -- 수정 전 재고 + 기존 주문량 >= 수정 후 주문량인지 확인
    IF current_avail_quantity + OLD.quantity >= NEW.quantity THEN
        UPDATE partsupp 
        SET availqty = current_avail_quantity - NEW.quantity + OLD.quantity 
        WHERE partkey = NEW.partkey AND suppkey = NEW.suppkey;
    ELSE
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Insufficient available quantity';
    END IF;
END$$
DELIMITER ;

2.2 주문 생성 전 재고 확보 확인

새로운 주문 항목을 입력할 때, 필요한 부품 수량이 공급 가능 수량보다 많은지 미리 판단합니다.

DELIMITER $$
CREATE TRIGGER check_stock_before_insert 
BEFORE INSERT ON lineitem
FOR EACH ROW 
BEGIN
    DECLARE stock_pool INT;
    
    SELECT availqty INTO stock_pool 
    FROM partsupp 
    WHERE partkey = NEW.partkey AND suppkey = NEW.suppkey;
    
    IF stock_pool >= NEW.quantity THEN
        UPDATE partsupp 
        SET availqty = stock_pool - NEW.quantity 
        WHERE partkey = NEW.partkey AND suppkey = NEW.suppkey;
    ELSE
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Available inventory depleted';
    END IF;
END$$
DELIMITER ;

2.3 주문 삭제 시 재고 반납 처리

주문 건이 취소되어 삭제되는 경우, 해당 품목의 사용되었던 수량을 공급자 재고로 다시 반환합니다.

DELIMITER $$
CREATE TRIGGER return_stock_before_delete 
BEFORE DELETE ON lineitem
FOR EACH ROW 
BEGIN
    UPDATE partsupp 
    SET availqty = availqty + OLD.quantity 
    WHERE partkey = OLD.partkey AND suppkey = OLD.suppkey;
END$$
DELIMITER ;

2.4 재고 관리 기능 검증

재고 부족 에러 발생 여부 및 정상적인 입출고 흐름이 트리거에 의해 올바르게 제어되는지 확인합니다.

# 연관 데이터 조회
SELECT ps.availqty, ps.partkey, ps.suppkey
FROM partsupp ps
JOIN lineitem l ON ps.partkey = l.partkey AND ps.suppkey = l.suppkey
WHERE l.orderkey = 1016 AND l.linenumber = 2;

# 재고 감소 유도 테스트
UPDATE lineitem SET quantity = 8000 WHERE orderkey = 1016 AND linenumber = 2;

# 초과 수량 삽입 시도 (실패 예상)
INSERT INTO lineitem(orderkey, partkey, suppkey, linenumber, quantity) 
VALUES (1016, 6440, 21004, 4, 8000);

# 정상 수량 삽입 성공
INSERT INTO lineitem(orderkey, partkey, suppkey, linenumber, quantity) 
VALUES (1016, 6440, 21004, 4, 80);

# 삭제 시 재고 복구 확인
DELETE FROM lineitem WHERE orderkey = 1016 AND linenumber = 4;
SELECT availqty FROM partsupp WHERE partkey = 6440 AND suppkey = 21004;