프로시저·함수·트리거

저장 프로시저, 함수, 트리거는 DB 서버에서 실행된다는 공통점만 있을 뿐 역할이 다릅니다.

프로시저는 명시적으로 호출하는 작업, 함수는 식 안에서 값을 계산하는 표현, 트리거는 DML 이벤트에 암묵적으로 반응하는 로직입니다.

가까이 있다는 이유로 모든 업무 규칙을 DB 로직에 넣으면 호출 경로·배포·관찰·테스트가 어려워집니다.

반대로 데이터 가까이에서 짧고 결정적으로 지켜야 하는 파생값에는 변경 전 트리거가 적합할 수 있습니다.

책임을 기능 이름이 아니라 오류 복구 경계로 판단합니다.

게시글을 PUBLISHED로 바꾸면 published_at을 채우려는 요구를 다룹니다.

변경 후 UPDATE 트리거가 같은 post_logic_demo를 다시 UPDATE하는 잘못된 구현은 MySQL 오류 1442를 냅니다.

개선안은 명시적 UPDATE와 검사를 기본으로 하고, 꼭 DB에서 파생해야 한다면 신규 값을 설정하는 변경 전 트리거로 제한합니다.

이 문서부터 여러 문장으로 된 프로시저와 트리거를 실행합니다.

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은 호출 구문이 사용 중인 테이블을 저장된 함수/트리거에서 다시 변경하는 것을 막습니다.

자기 테이블 재진입 트리거

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;

UPDATE가 트리거를 호출하지만 같은 테이블 재수정이 금지되어 원래 구문 전체에 오류가 발생합니다.

오류 실행 결과

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.

status after failure: DRAFT
published_at after failure: NULL

트리거 내부 구문만 오류가 난 것이 아니라 호출 UPDATE도 롤백됩니다.

호출 코드에서 published_at 변경이 보이지 않아 잠금·오류 위치를 추적하기 어렵고, 데이터 이관 도구도 동일 트리거를 암묵적으로 실행합니다.

같은 행의 신규 값을 조정하려면 변경 전 트리거에서 SET NEW.published_at = ...를 사용할 수 있습니다.

그러나 서비스가 상태 전이와 시각을 이미 하나의 UPDATE로 명시할 수 있다면 숨은 로직을 만들 이유가 약합니다.

호출 UPDATE와 숨은 UPDATE를 분리해 보기

호출 가시성 — DDL을 모르는 개발자가 UPDATE만 보고 모든 변경을 예측할 수 있는지 봅니다.
재진입 — 트리거가 호출 테이블을 다시 INSERT·UPDATE·DELETE하는지 확인합니다.
원자성 — 트리거 오류가 원래 구문과 트랜잭션에 어떤 롤백을 주는지 확인합니다.
배포 — 애플리케이션 버전과 DB 루틴 버전을 어떤 순서로 호환 배포하는지 적습니다.

DB 로직의 우선순위

항상 참인 행 규칙은 검사·FK·UNIQUE, 여러 변경의 업무 범위는 서비스가 호출하는 명시적 트랜잭션, 읽기 모양은 뷰를 우선 검토합니다.

프로시저는 권한 경계나 DB 가까운 배치가 명확할 때, 함수는 부작용 없는 결정 계산일 때, 트리거는 모든 쓰기 경로에 반드시 적용할 짧은 행 파생에 제한합니다.

루틴 안에 커밋을 숨기면 호출자가 더 큰 트랜잭션을 구성하기 어렵습니다.

오류를 삼키는 처리기도 부분 성공을 감춥니다.

트랜잭션 소유자와 오류 전파 규칙을 한 곳에서 분명히 합니다.

제약·생성 열·뷰를 먼저 검토하기

불변식 — 상태와 published_at 조합을 검사로 판단 가능한지 봅니다.
명시적 변경 — 서비스 UPDATE 한 문장에 상태와 시각을 함께 넣을 수 있는지 봅니다.
읽기 재사용 — 집계·표현만 필요하면 뷰가 적합한지 봅니다.
DB 로직 승인 — 숨은 실행의 이득이 배포·관찰 비용보다 큰 경우에만 루틴을 둡니다.

상태 전이와 불변식

트리거를 삭제하고 애플리케이션 트랜잭션의 UPDATE에 두 값을 함께 씁니다.

검사는 PUBLISHED일 때 published_at이 존재하고 그 외에는 NULL이라는 저장 상태를 보호합니다.

명시적 상태 변경과 CHECK

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;

호출 SQL만 읽어도 두 변경과 상태 전이 조건이 보이며 검사가 다른 쓰기 경로의 불완전한 행을 거절합니다.

개선 실행 결과

UPDATE: Query OK, 1 row affected

id | status | published_at
1          | PUBLISHED      | 2026-07-14 10:30:00.123456

invalid PUBLISHED + NULL attempt:
ERROR 3819 ... chk_logic_publish_pair

CURRENT_TIMESTAMP는 구문 시각이며 애플리케이션이 비즈니스 이벤트 시각을 이미 정했다면 매개변수를 전달합니다.

affected_rows 0은 이미 공개됐거나 예상 상태가 아니라는 뜻이므로 무조건 성공으로 처리하지 않습니다.

읽기 편의를 위해 v_published_posts 뷰를 둘 수 있지만 뷰 이름과 컬럼을 API처럼 버전 관리합니다.

뷰가 성능을 자동 개선하지 않으며 내부 쿼리 계획을 EXPLAIN해야 합니다.

트리거 제거 뒤 위반 경로 확인하기

트리거 목록 — SHOW TRIGGERS와 information_schema.ROUTINES에서 숨은 로직의 객체 목록을 확인합니다.
호출 치환 — 트리거가 하던 변경을 서비스 트랜잭션 SQL에 명시합니다.
제약 추가 — 기존 위반 행을 정리한 뒤 검사로 최종 상태를 보호합니다.
오류 관찰 — 루틴/제약 조건 이름, SQLState, 호출 쿼리를 같은 추적에 남깁니다.

저장 객체와 실행 권한

DB 루틴은 원본 저장소 밖에서 불일치할 수 있습니다.

현재 스키마의 트리거·루틴·DEFINER를 조회하고 마이그레이션 정의와 비교합니다.

트리거와 routine inventory

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;

개선 후 post_logic_demo를 수정하는 트리거는 0개이고 검사 정의가 SHOW CREATE TABLE에 나타나야 합니다.

남은 루틴의 DEFINER 계정이 존재하며 최소 권한인지 확인합니다.

함수 호출 횟수와 트리거 롤백 범위

저장된 함수는 SELECT 안에서 여러 번 호출될 수 있으므로 숨은 SQL과 비결정 동작이 비용을 폭발시킬 수 있습니다.

결정적 표시는 약속일 뿐 서버가 함수 의미를 자동 확인하지 않습니다.

트리거는 같은 트랜잭션에서 실행되어 원자성은 얻지만 독립 재시도·시간 초과 경계를 갖기 어렵습니다.

감사 로그를 트리거로 남길 때 원본 변경 롤백과 함께 감사도 사라지는 것이 요구와 맞는지 확인합니다.

DB 로직 배치 위치 비교

선택	얻는 효과	감수할 비용	적합한 조건
명시적 서비스 SQL	가시성·관찰·버전 통합	여러 쓰기 주체 조율	주요 쓰기 경로를 관리할 때
변경 전 트리거	모든 경로의 짧은 파생	숨은 실행과 배포	행 내부 결정적 보정일 때
프로시저	권한 경계·왕복 감소	DB 방언·트랜잭션 결합	DB 중심 배치가 명확할 때
뷰	읽기 규칙 재사용	성능 보장 없음	복잡한 투영을 공유할 때

1442와 불일치를 직접 재현하기

1442 재현 — 변경 후 트리거 생성부터 오류 UPDATE, 상태 미변경까지 한 스크립트로 기록합니다.
변경 전 비교 — 신규 published_at 설정 트리거가 가능한 이유와 여전히 숨은 비용인 이유를 씁니다.
루틴 오류 — SIGNAL SQLSTATE로 업무 오류를 전파하고 애플리케이션 매핑을 확인합니다.
불일치 검사 — 마이그레이션에 없는 트리거를 임시 생성해 객체 목록이 탐지하는지 확인합니다.

DEFINER·권한·내부 구문 관찰

루틴 DEFINER가 삭제되거나 권한이 바뀌면 운영에서만 오류가 발생할 수 있습니다.

SQL SECURITY, DEFINER, 마이그레이션 복원 순서를 배포 점검 목록에 둡니다.

DB 로직 성능은 애플리케이션 APM에서 한 쿼리로 뭉개질 수 있습니다.

performance_schema와 느린 로그에서 내부 구문을 관찰할 방법을 마련합니다.

복구·실행 순서·뷰 정렬의 예외

변경 전 트리거에서 AUTO_INCREMENT 신규 값은 시점에 따라 확정되지 않을 수 있으므로 식별자 사용을 검토합니다.
다중 트리거 실행 순서는 FOLLOWS/PRECEDES 의존을 만들 수 있어 가능한 수를 줄입니다.
mysqldump·복제·대량 적재가 트리거를 어떻게 실행하는지 복구 절차에서 검증합니다.
뷰에 ORDER BY를 숨겨도 바깥 쿼리 결과 순서가 자동 보장되지 않으므로 최종 SELECT가 정렬합니다.

이 장까지 회원 게시판의 실행 무결성 경계를 만들었습니다.

다음 장부터는 요구사항을 엔티티·관계로 번역해 애초에 불가능한 구조와 이상 현상을 줄이는 설계 단계로 이동합니다.

DB 저장 로직 도입 기준

판단 축	확인할 질문
가시성	호출 SQL에서 실제 변경과 오류 위치를 예측할 수 있는가?
대안	제약·생성 열·뷰·명시적 트랜잭션을 먼저 검토했는가?
소유권	루틴과 호출자 중 누가 커밋·롤백을 책임지는가?
배포	애플리케이션과 DB 객체의 호환 순서·롤백이 있는가?
운영	DEFINER·권한·느린 로그·불일치를 관찰하는가?

숨은 실행이 필요한 이유를 한 문장으로 설명하지 못하면 제약·명시적 트랜잭션·뷰로 되돌립니다.

루틴을 남길 때 호출, 트랜잭션 소유, 오류 전파, DEFINER, 관찰 경로를 함께 문서화합니다.

연습 문제

게시글의 view_count로 popularity_bucket을 LOW·MEDIUM·HIGH로 분류하려 합니다.

저장 컬럼+트리거, 생성 열, 뷰 계산을 비교하고 한 방법을 DDL로 구현하세요.

해설과 예시 답안

원본 값으로 항상 결정되고 별도 수정하지 않는다면 생성 열이 숨은 재UPDATE 없이 일관된 파생값을 제공합니다.

검색이 필요하면 인덱스 가능성도 검토합니다.

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);

view_count를 바꾸면 버킷이 자동 일치하고 직접 INSERT하려 하면 거절되는지 확인합니다.

분류 기준 변경 DDL 비용도 기록합니다.

핵심 정리

프로시저·함수·트리거는 호출 방식과 부작용 규칙이 다릅니다.
자기 테이블을 변경 후 트리거에서 다시 수정하면 MySQL 1442 오류가 납니다.
불변식은 제약, 업무 변경은 명시적 트랜잭션, 읽기 모양은 뷰를 우선합니다.
남은 DB 로직은 DEFINER·배포·오류·관찰 규칙을 함께 관리합니다.

다음 장에서는 회원 게시판 요구사항에서 엔티티와 관계를 찾는 개념 모델링을 시작합니다.

이 페이지의 목차