프로시저·함수·트리거

저장 프로시저(stored procedure)와 함수(stored function), 트리거(trigger)는 데이터베이스 서버에서 실행된다는 점만 같을 뿐 역할이 다릅니다. 프로시저는 명시적으로 호출하는 작업이고, 함수는 식 안에서 값을 계산하는 표현이며, 트리거는 DML이라는 사건에 암묵적으로 반응하는 로직입니다. 데이터와 가깝다는 이유로 모든 업무 규칙을 데이터베이스의 로직에 넣으면, 호출의 경로와 배포, 관찰과 시험이 어려워집니다.

반대로 데이터 가까이에서 짧고 결정적으로 지켜야 하는 파생값에는 BEFORE 트리거가 알맞을 수 있습니다. 이 글에서는 기능의 이름이 아니라, 오류가 났을 때 어디까지 되돌아가는지를 기준으로 판단합니다. 게시글을 PUBLISHED로 바꾸면 published_at을 채우려는 요구를, 같은 테이블을 다시 UPDATE하는 잘못된 트리거부터 시작해 살펴봅니다.

저장 객체를 만들기 전에

이 글부터 여러 문장으로 된 프로시저와 트리거를 실행합니다. DELIMITER //는 서버의 SQL 문법이 아니라, mysql 명령줄과 MySQL Workbench가 세미콜론이 든 정의 전체를 한 번에 보내도록 하는 클라이언트의 지시어입니다. JDBC나 마이그레이션 도구가 DELIMITER를 이해하지 못한다면, 구분자 줄을 지우고 CREATE TRIGGER ... BEGIN ... END 전체를 도구의 문장 하나로 넘깁니다.

실행하기 전에 SHOW GRANTS로 실습 계정에 CREATE ROUTINE, ALTER ROUTINE, EXECUTE, TRIGGER 권한이 있는지 확인합니다. 권한이 없다면 로컬의 실습용 관리 계정으로 객체를 만든 뒤, 일반 계정에는 필요한 EXECUTE만 줍니다.

SHOW GRANTS FOR CURRENT_USER();
SELECT VERSION() AS mysql_version, DATABASE() AS current_database;

DELIMITER 블록은 모두 스크립트로 한 번에 실행하고, 마지막에 반드시 DELIMITER ;로 되돌립니다. 웹 SQL 콘솔처럼 클라이언트의 기능을 알 수 없는 환경에서는 블록을 잘라 실행하지 않습니다.

자기 테이블을 다시 고치는 트리거

호출하는 쪽은 상태 열 하나만 바꾸었는데, 트리거가 같은 행을 다시 고치려 합니다. MySQL은 저장 함수나 트리거가, 자신을 호출한 문장이 쓰고 있는 테이블을 다시 변경하는 것을 금지합니다. 이 예제는 끝없는 재귀가 실행되는 것이 아니라, 그 제한 때문에 오류 1442가 나는 경우입니다.

DROP TABLE IF EXISTS post_logic_demo;

CREATE TABLE post_logic_demo (
  id BIGINT PRIMARY KEY,
  status VARCHAR(20) NOT NULL,
  view_count SMALLINT UNSIGNED NOT NULL,
  published_at DATETIME(6) NULL
);

INSERT INTO post_logic_demo
  (id, status, view_count, published_at)
VALUES (1, 'DRAFT', 30, NULL);

DELIMITER //
CREATE TRIGGER au_post_logic_published
AFTER UPDATE ON post_logic_demo
FOR EACH ROW
BEGIN
  IF NEW.status = 'PUBLISHED'
     AND OLD.status <> 'PUBLISHED' THEN
    UPDATE post_logic_demo
    SET published_at = CURRENT_TIMESTAMP(6)
    WHERE id = NEW.id;
  END IF;
END//
DELIMITER ;

UPDATE post_logic_demo
SET status = 'PUBLISHED'
WHERE id = 1;

ERROR 1442 (HY000): Can't update table 'post_logic_demo' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

UPDATE가 트리거를 부르지만 같은 테이블을 다시 고치는 것이 금지되어, 원래의 문장 전체가 오류로 끝납니다. 이 CREATE TABLE은 ENGINE을 생략했으므로 @@default_storage_engine이 InnoDB인 환경을 전제로 하며, InnoDB에서는 트리거의 오류로 호출한 UPDATE의 변경도 롤백되어 status는 DRAFT, published_at은 NULL로 남습니다.

이 구조의 문제는 오류만이 아닙니다. 호출하는 코드에서는 published_at이 바뀐다는 것이 보이지 않아 잠금과 오류의 위치를 추적하기 어렵고, 데이터를 옮기는 도구도 같은 트리거를 암묵적으로 실행합니다. 같은 행의 새 값을 조정하려면 BEFORE 트리거에서 SET NEW.published_at = ...를 쓸 수 있지만, 서비스가 상태의 변경과 시각을 이미 UPDATE 하나에 명시할 수 있다면 숨은 로직을 만들 까닭이 약합니다.

트리거를 검토할 때는 DDL을 모르는 개발자가 UPDATE만 보고 모든 변경을 예측할 수 있는지, 트리거가 호출한 테이블을 다시 INSERT나 UPDATE, DELETE하는지 봅니다. 트리거의 오류가 원래의 문장과 트랜잭션을 어디까지 되돌리는지 확인하고, 애플리케이션의 버전과 데이터베이스 루틴의 버전을 어떤 순서로 배포해야 서로 맞는지도 적어 둡니다.

더 깊이: 트리거가 실행되는 쓰기와 실행되지 않는 쓰기

트리거의 사건은 문장의 이름이 아니라 행이 어떻게 바뀌는지로 정해집니다. INSERT 트리거는 INSERT뿐 아니라 LOAD DATA와 REPLACE로 행이 들어올 때도 실행되고, DELETE 트리거는 REPLACE가 기존 행을 지울 때도 실행됩니다. INSERT ... ON DUPLICATE KEY UPDATE는 행마다 BEFORE INSERT 트리거를 먼저 실행한 뒤, 중복 키가 없으면 AFTER INSERT를, 있으면 BEFORE UPDATE와 AFTER UPDATE를 실행합니다. 그래서 BEFORE INSERT 트리거는 결국 UPDATE로 끝난 행에서도 실행됩니다.

반대로 행이 사라지거나 바뀌어도 트리거가 실행되지 않는 경로가 있습니다. DROP TABLE과 TRUNCATE TABLE은 DELETE를 쓰지 않으므로 DELETE 트리거를 실행하지 않고, 파티션을 지울 때도 마찬가지입니다. MySQL 8.4의 CREATE TRIGGER는 외래 키의 연쇄 동작도 트리거를 실행하지 않는다고 적습니다. 감사 기록을 트리거로 남긴다면, 부모를 지울 때 ON DELETE CASCADE로 함께 지워진 자식 행은 감사 테이블에 남지 않는다는 뜻입니다.

실패와 순서의 규칙도 정해져 있습니다. BEFORE 트리거는 행을 넣거나 바꾸려는 시도만으로 실행되고, 실패하면 그 행의 작업은 실행되지 않습니다. AFTER 트리거는 BEFORE 트리거와 행의 작업이 모두 성공했을 때만 실행되며, 어느 쪽 트리거든 오류가 나면 문장 전체가 실패해 InnoDB에서는 그 문장의 변경이 모두 롤백됩니다. 같은 사건과 시점에 트리거를 여러 개 만들 수 있는데, 기본으로는 만든 순서대로 실행되고 FOLLOWS나 PRECEDES로 순서를 바꿀 수 있습니다.

무엇을 먼저 검토하는가

언제나 참인 행의 규칙은 CHECK와 외래 키, UNIQUE로, 여러 변경을 묶는 업무의 범위는 서비스가 호출하는 명시적인 트랜잭션으로, 읽기의 모양은 뷰로 먼저 검토합니다. 프로시저는 권한의 범위나 데이터베이스 가까이의 배치 작업이 분명할 때, 함수는 부작용이 없는 결정적인 계산일 때, 트리거는 모든 쓰기 경로에 반드시 적용해야 하는 짧은 행의 파생일 때로 제한합니다.

루틴 안에 COMMIT을 숨기면 호출하는 쪽이 더 큰 트랜잭션을 짜기 어렵고, 오류를 삼키는 핸들러는 부분적인 성공을 감춥니다. 누가 트랜잭션을 시작하고 끝내는지, 오류를 어떻게 전달하는지를 한곳에서 분명히 정합니다.

명시적인 UPDATE와 CHECK

트리거를 지우고, 애플리케이션 트랜잭션의 UPDATE에 두 값을 함께 씁니다. CHECK는 PUBLISHED일 때는 published_at이 있고, 그 밖에는 NULL이라는 저장 상태를 보호합니다.

DROP TRIGGER IF EXISTS au_post_logic_published;

ALTER TABLE post_logic_demo
  ADD CONSTRAINT chk_logic_publish_pair
  CHECK (
    (status = 'PUBLISHED' AND published_at IS NOT NULL)
    OR
    (status <> 'PUBLISHED' AND published_at IS NULL)
  );

START TRANSACTION;
UPDATE post_logic_demo
SET status = 'PUBLISHED',
    published_at = CURRENT_TIMESTAMP(6)
WHERE id = 1
  AND status = 'DRAFT';
COMMIT;

SELECT id, status, published_at IS NOT NULL AS has_published_at
FROM post_logic_demo;

id | status    | has_published_at
1  | PUBLISHED | 1

호출한 SQL만 읽어도 두 변경과 상태가 바뀌는 조건이 보이고, CHECK는 다른 쓰기 경로가 만든 불완전한 행을 거부합니다. 예를 들어 UPDATE post_logic_demo SET published_at = NULL WHERE id = 1;은 공개 상태인데 게시 시각을 지우려 하므로 오류 3819(Check constraint 'chk_logic_publish_pair' is violated.)로 거부됩니다. 게시 시각의 값은 실행한 시각에 따라 다르므로, 결과에서는 값이 있는지만 보였습니다.

CURRENT_TIMESTAMP는 문장을 실행한 시각이므로, 애플리케이션이 업무상의 사건 시각을 이미 정했다면 그 값을 파라미터로 넘깁니다. 영향받은 행이 0이라는 결과에는 대상 id가 없는 경우와 상태 조건이 맞지 않는 경우가 함께 들어 있으므로, 무조건 성공으로 처리하지 않습니다.

명시적으로 호출하는 프로시저

여러 서비스가 같은 상태 변경을 쓴다면, 그 변경을 프로시저로 만들어 명시적으로 호출하게 할 수 있습니다. 아래 프로시저는 초안일 때만 공개하고, 바꾼 행이 없으면 SIGNAL로 업무의 오류를 호출하는 쪽에 알립니다. COMMIT은 넣지 않아, 트랜잭션은 호출하는 쪽이 맡게 합니다.

DROP PROCEDURE IF EXISTS publish_logic_post;

DELIMITER //
CREATE PROCEDURE publish_logic_post(IN p_id BIGINT)
BEGIN
  UPDATE post_logic_demo
  SET status = 'PUBLISHED',
      published_at = CURRENT_TIMESTAMP(6)
  WHERE id = p_id
    AND status = 'DRAFT';

  IF ROW_COUNT() = 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'post is not a draft';
  END IF;
END//
DELIMITER ;

CALL publish_logic_post(1);

ERROR 1644 (45000): post is not a draft

게시글 1은 앞에서 이미 공개되었으므로 UPDATE가 0행을 바꾸고, 프로시저는 SQLSTATE 45000의 사용자 정의 오류를 냅니다. 이 SQLSTATE에 오류 번호를 따로 정하지 않으면 MySQL은 1644를 붙이며, 애플리케이션은 이 번호와 SQLSTATE를 "공개할 수 없음"이라는 업무의 오류로 바꿉니다. 프로시저는 호출이 SQL에 드러나므로 트리거보다 추적하기 쉽지만, 제품마다 문법이 다르고 애플리케이션과 따로 배포해야 한다는 비용이 있습니다.

함수에도 따져 볼 점이 있습니다. 저장 함수는 SELECT 안에서 행마다 여러 번 호출될 수 있어, 안에 숨은 SQL이나 실행마다 결과가 다른 동작이 비용을 크게 늘릴 수 있습니다. 함수에 붙이는 DETERMINISTIC은 만든 사람의 약속일 뿐 서버가 그 뜻을 검사하지 않으며, 바이너리 로그가 켜진 서버에서는 이런 특성을 선언하지 않은 함수를 만들면 오류 1418로 거부됩니다.

저장 객체와 실행 권한

데이터베이스의 루틴은 소스 저장소의 정의와 어긋날 수 있습니다. 지금 스키마에 있는 트리거와 루틴, 그 DEFINER를 조회해 마이그레이션의 정의와 비교합니다.

SHOW TRIGGERS FROM board_lab;

SELECT
  routine_name,
  routine_type,
  security_type,
  definer,
  sql_data_access
FROM information_schema.routines
WHERE routine_schema = DATABASE()
ORDER BY routine_type, routine_name;

SHOW CREATE TABLE post_logic_demo;

SHOW TRIGGERS에는 post_logic_demo를 고치는 트리거가 남아 있지 않아야 하고, 루틴 목록에는 방금 만든 publish_logic_post가 PROCEDURE로 나타납니다. SHOW CREATE TABLE의 결과에는 chk_logic_publish_pair의 정의가 보여야 합니다. 남은 루틴의 DEFINER 계정이 실제로 있는지, 그 권한이 최소한인지도 확인합니다.

InnoDB에서 원래의 쓰기와 트리거의 쓰기는 같은 트랜잭션에 속하므로, 트리거만 따로 재시도하거나 시간을 제한하기 어렵습니다. 감사 로그를 트리거로 남긴다면, 원래의 변경이 롤백될 때 감사 기록도 함께 사라지는 것이 요구에 맞는지 확인합니다.

루틴의 DEFINER가 지워지거나 권한이 바뀌면 운영에서만 오류가 날 수 있으므로, SQL SECURITY와 DEFINER, 마이그레이션을 되돌리는 순서를 배포 점검 목록에 둡니다. 데이터베이스 로직의 성능은 애플리케이션의 성능 도구에서 쿼리 하나로 합쳐 보일 수 있으므로, performance_schema와 느린 쿼리 로그에서 안의 문장을 관찰할 방법을 마련합니다.

예외도 있습니다. BEFORE INSERT 트리거에서는 AUTO_INCREMENT의 새 값이 아직 정해지지 않았을 수 있으므로 그 값을 쓰는 로직을 조심합니다. 같은 사건에 트리거가 여럿이면 FOLLOWS와 PRECEDES로 순서가 서로 얽히므로 수를 줄이고, mysqldump나 복제, 대량 적재가 트리거를 어떻게 실행하는지 복구 절차에서 확인합니다. 뷰에 ORDER BY를 숨겨도 바깥 쿼리의 결과 순서는 보장되지 않으므로, 최종 SELECT가 정렬합니다.

숨은 실행이 필요한 이유를 한 문장으로 설명하지 못한다면, 제약과 명시적인 트랜잭션, 뷰로 되돌립니다. 루틴을 남긴다면 호출 방법과 트랜잭션을 맡는 쪽, 오류의 전달, DEFINER, 관찰의 경로를 함께 문서로 남깁니다.

정리

• 프로시저와 함수, 트리거는 호출되는 방식과 부작용의 규칙이 다릅니다.
• AFTER 트리거에서 자기 테이블을 다시 고치면 MySQL은 오류 1442로 원래 문장 전체를 거부합니다.
• 불변식은 제약으로, 업무의 변경은 명시적인 트랜잭션으로, 읽기의 모양은 뷰로 먼저 해결합니다.
• 프로시저는 트랜잭션을 호출하는 쪽에 맡기고, 업무의 오류는 SIGNAL로 알립니다.
• 남겨 둔 데이터베이스 로직은 DEFINER와 배포, 오류, 관찰의 규칙과 함께 관리합니다.

연습 문제

1. 조회수에 따른 인기 구간

게시글의 view_count로 popularity_bucket을 LOW, MEDIUM, HIGH로 나누려 합니다. 저장 열과 트리거, 생성 열, 뷰의 계산을 비교하고, 한 방법을 DDL로 구현해 보세요.

ALTER TABLE post_logic_demo
  ADD COLUMN popularity_bucket VARCHAR(8)
    GENERATED ALWAYS AS (
      CASE
        WHEN view_count < 100 THEN 'LOW'
        WHEN view_count < 1000 THEN 'MEDIUM'
        ELSE 'HIGH'
      END
    ) STORED,
  ADD INDEX ix_logic_popularity_bucket (popularity_bucket);

SELECT id, view_count, popularity_bucket FROM post_logic_demo;

id | view_count | popularity_bucket
1  | 30         | LOW

2. BEFORE 트리거로 바꾸면

본문의 요구를 BEFORE UPDATE 트리거로 구현한다면, 트리거 안에서 무엇을 해야 하나요? 오류 1442가 나지 않는 이유와 그래도 남는 비용을 설명해 보세요.

DELIMITER //
CREATE TRIGGER bu_post_logic_published
BEFORE UPDATE ON post_logic_demo
FOR EACH ROW
BEGIN
  IF NEW.status = 'PUBLISHED'
     AND OLD.status <> 'PUBLISHED'
     AND NEW.published_at IS NULL THEN
    SET NEW.published_at = CURRENT_TIMESTAMP(6);
  END IF;
END//
DELIMITER ;

다음 글 로그·체크포인트와 장애 회복에서는 커밋한 변경이 장애 뒤에도 남는 지속성을, 로그와 체크포인트로 어떻게 보장하는지 다룹니다.