PostgreSQL Trigger와 Function으로 자동화 로직 구현

트리거와 함수의 관계

PostgreSQL 트리거는 INSERT, UPDATE, DELETE 이벤트가 발생할 때 자동으로 함수를 실행하는 메커니즘입니다. 비즈니스 로직을 데이터베이스 레벨에서 강제하여, 어떤 애플리케이션에서 데이터를 변경하든 일관된 규칙을 보장합니다.

감사 로그 자동 기록

-- 감사 로그 테이블
CREATE TABLE audit_log (
    id          BIGSERIAL PRIMARY KEY,
    table_name  TEXT NOT NULL,
    operation   TEXT NOT NULL,
    row_id      INTEGER,
    old_data    JSONB,
    new_data    JSONB,
    changed_by  TEXT DEFAULT current_user,
    changed_at  TIMESTAMPTZ DEFAULT now()
);

-- 감사 트리거 함수
CREATE OR REPLACE FUNCTION fn_audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO audit_log (table_name, operation, row_id, new_data)
        VALUES (TG_TABLE_NAME, 'INSERT', NEW.id, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
        VALUES (TG_TABLE_NAME, 'UPDATE', NEW.id, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, row_id, old_data)
        VALUES (TG_TABLE_NAME, 'DELETE', OLD.id, to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 여러 테이블에 트리거 적용
CREATE TRIGGER trg_users_audit
    AFTER INSERT OR UPDATE OR DELETE ON users
    FOR EACH ROW EXECUTE FUNCTION fn_audit_trigger();

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

자동 updated_at 갱신

CREATE OR REPLACE FUNCTION fn_update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION fn_update_timestamp();

데이터 검증 트리거

-- 재고 검증: 음수 재고 방지
CREATE OR REPLACE FUNCTION fn_check_inventory()
RETURNS TRIGGER AS $$
DECLARE
    current_stock INTEGER;
BEGIN
    SELECT stock INTO current_stock
    FROM products WHERE id = NEW.product_id;

    IF current_stock < NEW.quantity THEN
        RAISE EXCEPTION '재고 부족: 상품 %, 현재 재고 %, 요청 수량 %',
            NEW.product_id, current_stock, NEW.quantity;
    END IF;

    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_check_inventory
    BEFORE INSERT ON order_items
    FOR EACH ROW EXECUTE FUNCTION fn_check_inventory();

통계 자동 갱신

CREATE OR REPLACE FUNCTION fn_update_comment_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_comment_count
    AFTER INSERT OR DELETE ON comments
    FOR EACH ROW EXECUTE FUNCTION fn_update_comment_count();

트리거 관리 명령어

• BEFORE 트리거는 데이터를 수정/검증할 수 있고, AFTER 트리거는 후속 작업에 적합합니다
• 트리거 함수에서 RAISE EXCEPTION으로 트랜잭션을 롤백할 수 있습니다
• 대량 데이터 처리 시 트리거가 성능 병목이 될 수 있으므로 DISABLE/ENABLE을 활용합니다
• STATEMENT 레벨 트리거는 FOR EACH ROW 대신 한 번만 실행되어 배치 처리에 효율적입니다
• pg_trigger 카탈로그에서 트리거 상태를 모니터링할 수 있습니다