MariaDB 강좌 / 저장 프로시저·함수·트리거의 차이와 사용 방법

저장 프로시저는 CALL로 실행하는 서버 측 작업 단위이고, 저장 함수는 SQL 식에서 호출해 값을 반환하며, 트리거는 테이블의 변경에 반응해 자동 실행됩니다. 같은 SQL을 서버에 저장한다는 공통점이 있지만 호출 시점, 반환 방식, 권한과 트랜잭션 책임이 다르므로 구분해 사용해야 합니다.

프로시저·함수·트리거를 선택하는 기준

프로시저가 항상 데이터를 바꾸거나 함수가 항상 순수 계산만 해야 하는 것은 아닙니다. 이 실습에서는 역할을 분명히 보여 주기 위해 프로시저로 재고를 변경하고 함수로 금액을 계산하며 트리거로 변경 전후 값을 기록합니다. MariaDB 트리거는 행마다 실행되므로 한 문장이 많은 행을 바꾸면 기록 비용도 커집니다.

서버에 숨겨진 동작이 늘면 장애 원인을 추적하기 어려울 수 있습니다. 호출자가 명확한 프로시저와 자동 실행되는 트리거를 혼동하지 말고, 애플리케이션 검증만으로 충분한 규칙인지와 여러 입력 경로에서 반드시 보장해야 하는 규칙인지를 먼저 구분하세요.

권한과 실습 테이블 준비하기

전용 appops_lab에 CREATE ROUTINE·TRIGGER와 필요한 테이블 권한이 있는 계정으로 연결합니다. 루틴 호출에는 EXECUTE도 필요합니다. 계정과 권한 설정을 참고하되 전역 관리자 권한을 애플리케이션에 부여하지 마세요. 바이너리 로그 정책에 따라 함수 생성이 추가로 제한되면 DBA가 해당 배포 정책을 확인해야 하며, 실습 성공을 위해 서버 전체의 신뢰 설정을 무조건 완화하지 않습니다.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE routine_stock (
  product_id INT NOT NULL PRIMARY KEY,
  stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB;
CREATE TABLE routine_audit (
  audit_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  old_stock INT NOT NULL,
  new_stock INT NOT NULL
) ENGINE=InnoDB;
INSERT INTO routine_stock VALUES (1, 8);

두 테이블은 모두 트랜잭션을 지원하는 InnoDB입니다. 주 테이블의 재고와 트리거가 쓰는 감사 행을 함께 되돌리는 실습을 하기 위한 조건입니다. 같은 이름의 실습 객체가 이미 있으면 데이터와 정의를 먼저 조회하고 생성문 중복 실행을 피하세요.

세 객체를 완전한 SQL로 만들기

다음 코드는 mariadb 명령줄 클라이언트의 스크립트 실행 형식입니다. DELIMITER는 서버 SQL 문장이 아니라 클라이언트가 문장 끝을 판단하는 설정입니다. SQL 편집기에서는 제품의 스크립트 실행 기능을 사용하고, Python 드라이버에서는 DELIMITER 줄과 끝의 // 없이 CREATE 문 하나를 한 번에 전달해야 합니다.

DELIMITER //
CREATE FUNCTION line_total(p_price DECIMAL(10,2), p_qty INT)
RETURNS DECIMAL(12,2)
DETERMINISTIC NO SQL SQL SECURITY INVOKER
BEGIN
  IF p_price IS NULL OR p_qty IS NULL OR p_price < 0 OR p_qty < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonnegative price and quantity required';
  END IF;
  RETURN ROUND(p_price * p_qty, 2);
END//

CREATE PROCEDURE adjust_stock(IN p_product_id INT, IN p_delta INT)
SQL SECURITY INVOKER
MODIFIES SQL DATA
BEGIN
  IF p_delta IS NULL OR p_delta = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Nonzero stock delta required';
  END IF;
  UPDATE routine_stock SET stock = stock + p_delta
  WHERE product_id = p_product_id AND stock + p_delta >= 0;
  IF ROW_COUNT() <> 1 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Missing product or insufficient stock';
  END IF;
END//

CREATE TRIGGER audit_stock_update
AFTER UPDATE ON routine_stock FOR EACH ROW
BEGIN
  IF OLD.stock <> NEW.stock THEN
    INSERT INTO routine_audit (product_id, old_stock, new_stock)
    VALUES (NEW.product_id, OLD.stock, NEW.stock);
  END IF;
END//
DELIMITER ;

line_total은 음수나 NULL 입력을 거부하고 두 자리 금액을 반환합니다. 선언한 DECIMAL 범위가 충분하지 않은 큰 주문 금액은 별도로 설계해야 합니다. DETERMINISTIC NO SQL은 이 계산의 성격을 표현하며 실제 구현과 일치해야 합니다. 자세한 조건은 CREATE FUNCTION 문서를 확인할 수 있습니다.

adjust_stock은 행이 존재하고 재고가 음수가 되지 않는 경우에만 UPDATE합니다. 조건을 충족하는 행이 하나가 아니면 SIGNAL로 업무 오류를 냅니다. ROW_COUNT()는 UPDATE 직후에 읽어야 하며 다른 SQL을 끼워 넣어 결과를 바꾸지 않습니다. CREATE PROCEDURE 문서는 매개변수와 권한 실행 문맥을 설명합니다.

트리거는 실제로 재고가 바뀐 행에만 감사 행을 삽입합니다. 이 기록은 변경 추적 예제이며 접근을 통제한 독립 감사 시스템이나 변조 방지 기능을 제공하는 것은 아닙니다. CREATE TRIGGER 문서에서 OLD·NEW 값과 실행 시점을 확인할 수 있습니다.

ROLLBACK과 COMMIT에서 두 테이블 비교하기

SELECT line_total(12.50, 3) AS amount;
START TRANSACTION;
CALL adjust_stock(1, -3);
SELECT product_id, stock FROM routine_stock;
SELECT product_id, old_stock, new_stock FROM routine_audit;
ROLLBACK;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT COUNT(*) AS audit_rows FROM routine_audit;

첫 금액은 37.50입니다. 트랜잭션 안에서는 재고가 8에서 5가 되고 감사 행에 8·5가 기록됩니다. ROLLBACK 뒤에는 재고가 8이고 감사 행 수가 0이어야 합니다. 자동 증가 번호는 롤백되어도 연속성을 보장하지 않으므로 감사 ID의 빈 번호를 오류로 판단하지 않습니다.

START TRANSACTION;
CALL adjust_stock(1, -2);
COMMIT;
SELECT stock FROM routine_stock WHERE product_id = 1;
SELECT old_stock, new_stock FROM routine_audit ORDER BY audit_id;

확정 후 재고는 6이고 감사 테이블에는 8 → 6 변경 한 건이 남습니다. 프로시저 안에 COMMIT을 넣지 않았기 때문에 호출자가 여러 작업을 하나의 트랜잭션으로 묶을 수 있습니다. 트리거는 자신을 일으킨 문장의 트랜잭션 문맥에서 실행되며 독립적으로 COMMIT하지 않습니다. InnoDB 트랜잭션과 확정·취소를 함께 이해해야 합니다.

오류와 실행 권한을 확인하기

다음 문장은 각각 따로 실행하세요. 첫 문장은 현재 재고 6보다 많이 차감하므로, 두 번째는 음수 단가이므로 SQLSTATE 45000 업무 오류가 예상됩니다. 실패 뒤 재고와 감사 행이 앞 단계의 6과 한 건을 유지하는지 확인합니다.

CALL adjust_stock(1, -99);
SELECT line_total(-1, 2);

이 함수와 프로시저는 SQL SECURITY INVOKER로 선언했으므로 호출자의 테이블 권한을 사용합니다. EXECUTE만 있고 UPDATE가 없는 계정은 이 프로시저로 재고를 수정할 수 없습니다. 반면 트리거는 DEFINER의 권한 문맥을 사용하며 INVOKER로 선언하지 않습니다. 따라서 트리거 정의자와 계정 수명, 필요한 테이블 접근 권한도 함께 관리해야 합니다.

트리거 제한 사항에 따라 트리거에서 대상 테이블을 다시 수정하거나 별도 트랜잭션 제어를 넣는 설계에는 제한이 있습니다. 트리거에 외부 API 호출이나 복잡한 장기 작업을 넣기보다, SQL 트랜잭션 안에서 필요한 짧은 작업인지 먼저 판단하세요.

정의 확인과 실습 객체 정리하기

SHOW CREATE PROCEDURE adjust_stock;
SHOW CREATE FUNCTION line_total;
SHOW TRIGGERS FROM appops_lab;

정의·실행 계정·보안 문맥을 기록하고 백업·복원 시 루틴과 트리거가 포함되었는지 확인합니다. DEFINER 계정이 없는 다른 서버에서 복원하면 SQL 정의가 있어도 실행 권한 문제가 생길 수 있습니다. 검증을 마친 후 이 글의 실습 객체만 다음 순서로 제거할 수 있습니다.

DROP TRIGGER appops_lab.audit_stock_update;
DROP PROCEDURE appops_lab.adjust_stock;
DROP FUNCTION appops_lab.line_total;
DROP TABLE appops_lab.routine_audit;
DROP TABLE appops_lab.routine_stock;

DROP은 일반 ROLLBACK으로 복구되지 않습니다. 업무 데이터가 섞이지 않은 전용 실습 객체인지 확인한 뒤 정리하고, 운영 루틴의 변경은 호출 애플리케이션과 배포·복구 절차까지 함께 검토하세요.