트리거 — BEFORE·AFTER·ROW·STATEMENT

트리거는 INSERT·UPDATE·DELETE 마다 자동으로 함수를 실행. 18편의 트리거 함수를 본격적으로 다룹니다. updated_at 자동 갱신·audit 로그·검증 — 잘 쓰면 강력하지만 남용하면 디버깅 지옥. 19편은 트리거의 모든 종류와 흔한 안티패턴.

트리거의 4가지 축

updated_at 자동 갱신 — 가장 자주 쓰는 패턴

-- 1) 트리거 함수
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- 2) 여러 테이블에 같은 트리거
CREATE TRIGGER trg_users_upd
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER trg_posts_upd
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

NEW · OLD — 변경 전/후 행

CREATE OR REPLACE FUNCTION validate_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  -- TG_OP : 'INSERT', 'UPDATE', 'DELETE'
  IF TG_OP = 'UPDATE' THEN
    -- NEW.col : 변경 후 값
    -- OLD.col : 변경 전 값
    IF NEW.email != OLD.email THEN
      INSERT INTO email_changes (user_id, old, new, changed_at)
      VALUES (OLD.id, OLD.email, NEW.email, NOW());
    END IF;
  END IF;

  RETURN NEW;       -- BEFORE 트리거에서 NEW 수정 가능
                    -- DELETE 면 OLD 반환
END;
$$;

BEFORE vs AFTER — 한 줄 차이

ROW vs STATEMENT

-- ROW 레벨 — 영향받는 각 행마다 실행
CREATE TRIGGER ... FOR EACH ROW EXECUTE FUNCTION fn();
-- UPDATE 가 1만 행 영향이면 fn() 도 1만 번 실행

-- STATEMENT 레벨 — 명령 한 번에 한 번
CREATE TRIGGER ... FOR EACH STATEMENT EXECUTE FUNCTION fn();
-- UPDATE 한 번에 fn() 도 한 번

-- STATEMENT 에서 영향받은 행 전체 보려면 transition table (PG10+)
CREATE TRIGGER trg_audit_batch
AFTER UPDATE ON orders
REFERENCING NEW TABLE AS new_rows OLD TABLE AS old_rows
FOR EACH STATEMENT
EXECUTE FUNCTION audit_changes();

WHEN 조건 — 일부만 발동

-- balance 가 실제로 바뀐 경우만
CREATE TRIGGER trg_balance_change
AFTER UPDATE OF balance ON accounts
FOR EACH ROW
WHEN (OLD.balance IS DISTINCT FROM NEW.balance)
EXECUTE FUNCTION log_balance_change();

-- UPDATE OF balance — balance 컬럼이 SET 절에 있는 경우만

실전 — audit 로그 패턴

-- 1) audit 테이블
CREATE TABLE audit_log (
  id        BIGSERIAL PRIMARY KEY,
  table_name TEXT NOT NULL,
  op        TEXT NOT NULL,        -- INSERT/UPDATE/DELETE
  row_id    BIGINT,
  before    JSONB,
  after     JSONB,
  changed_by TEXT,
  changed_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2) 범용 트리거 함수
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO audit_log (table_name, op, row_id, before, changed_by)
    VALUES (TG_TABLE_NAME, 'DELETE', OLD.id, row_to_json(OLD)::jsonb, current_user);
    RETURN OLD;
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, op, row_id, after, changed_by)
    VALUES (TG_TABLE_NAME, 'INSERT', NEW.id, row_to_json(NEW)::jsonb, current_user);
  ELSE
    INSERT INTO audit_log (table_name, op, row_id, before, after, changed_by)
    VALUES (TG_TABLE_NAME, 'UPDATE', NEW.id,
            row_to_json(OLD)::jsonb, row_to_json(NEW)::jsonb, current_user);
  END IF;
  RETURN NEW;
END;
$$;

-- 3) 추적 대상 테이블에 붙이기
CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_changes();

CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_changes();

트리거 목록 확인

-- 테이블의 트리거
\d+ users

-- 전체 사용자 트리거
SELECT trigger_name, event_manipulation, action_timing, action_statement
FROM   information_schema.triggers
WHERE  trigger_schema = 'public'
ORDER BY event_object_table, trigger_name;

-- 트리거 비활성화 (실험·복구)
ALTER TABLE users DISABLE TRIGGER trg_users_audit;
ALTER TABLE users ENABLE  TRIGGER trg_users_audit;

-- 모두
ALTER TABLE users DISABLE TRIGGER ALL;

흔한 안티패턴 5가지

트리거의 좋은 용도 — 3가지

• 자동 컬럼 — updated_at, version, search_vector 자동 갱신.
• audit 로그 — 누가·언제·뭘 바꿨나 추적.
• 제약·검증 — CHECK 로 표현 못하는 복잡한 검증 (다른 테이블 참조 등).