Триггеры в PostgreSQL: создание, виды и практические примеры

Триггер — это автоматически выполняемый код при изменении данных (INSERT, UPDATE, DELETE). В отличие от приложения, триггер срабатывает всегда — независимо от того, кто изменил данные.

Анатомия триггера

Триггер состоит из двух частей:

1. Триггерная функция — код на PL/pgSQL, возвращает TRIGGER
2. Триггер — привязка функции к таблице и событию

-- Шаг 1: функция
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Шаг 2: триггер
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION trigger_set_updated_at();

Виды триггеров

BEFORE vs AFTER

-- BEFORE: срабатывает ДО изменения, можно изменить NEW
BEFORE INSERT OR UPDATE ON table_name

-- AFTER: срабатывает ПОСЛЕ изменения, нельзя изменить строку
AFTER INSERT OR UPDATE OR DELETE ON table_name

В BEFORE-триггере:

• RETURN NEW — продолжить операцию с (возможно изменённой) строкой
• RETURN NULL — отменить операцию для этой строки (только FOR EACH ROW)
• RETURN OLD — оставить старые данные

FOR EACH ROW vs FOR EACH STATEMENT

-- FOR EACH ROW: вызывается для каждой изменённой строки
FOR EACH ROW

-- FOR EACH STATEMENT: вызывается один раз на весь DML-запрос
FOR EACH STATEMENT

OLD и NEW

В триггерных функциях доступны специальные переменные:

• NEW — новая строка (для INSERT, UPDATE)
• OLD — старая строка (для UPDATE, DELETE)

-- Пример: логируем что изменилось
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.price <> NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price, changed_at)
        VALUES (OLD.id, OLD.price, NEW.price, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER track_price_changes
BEFORE UPDATE ON products
FOR EACH ROW
WHEN (OLD.price IS DISTINCT FROM NEW.price)  -- только при реальном изменении цены
EXECUTE FUNCTION log_price_change();

Практический кейс 1: Автоматический updated_at

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

-- Применить ко всем нужным таблицам
CREATE TRIGGER set_updated_at BEFORE UPDATE ON users    FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON orders   FOR EACH ROW EXECUTE FUNCTION set_updated_at();
CREATE TRIGGER set_updated_at BEFORE UPDATE ON products FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Практический кейс 2: Полный аудит изменений

-- Таблица аудита
CREATE TABLE audit_log (
    id          BIGSERIAL PRIMARY KEY,
    table_name  TEXT,
    operation   TEXT,  -- INSERT / UPDATE / DELETE
    row_id      BIGINT,
    old_data    JSONB,
    new_data    JSONB,
    changed_by  TEXT DEFAULT current_user,
    changed_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Универсальная триггерная функция
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, operation, row_id, old_data, new_data)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        CASE TG_OP WHEN 'DELETE' THEN OLD.id ELSE NEW.id END,
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD)::jsonb END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW)::jsonb END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Применить к таблице
CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Практический кейс 3: Мягкое удаление

-- Перенаправить DELETE в UPDATE
CREATE OR REPLACE FUNCTION soft_delete()
RETURNS TRIGGER AS $$
BEGIN
    -- Вместо удаления — пометка
    UPDATE users SET deleted_at = NOW() WHERE id = OLD.id;
    RETURN NULL;  -- Отменить реальное DELETE
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_soft_delete
BEFORE DELETE ON users
FOR EACH ROW
WHEN (OLD.deleted_at IS NULL)  -- только если ещё не удалён
EXECUTE FUNCTION soft_delete();

Практический кейс 4: Валидация данных

CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
    -- Проверка суммы
    IF NEW.amount <= 0 THEN
        RAISE EXCEPTION 'Order amount must be positive, got: %', NEW.amount;
    END IF;

    -- Проверка что пользователь активен
    IF NOT EXISTS (SELECT 1 FROM users WHERE id = NEW.user_id AND is_active = true) THEN
        RAISE EXCEPTION 'Cannot create order for inactive user %', NEW.user_id;
    END IF;

    -- Нормализация статуса
    NEW.status := LOWER(NEW.status);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_order
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION validate_order();

Практический кейс 5: Обновление счётчика

-- Поддерживать счётчик заказов в таблице users
CREATE OR REPLACE FUNCTION update_order_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE users SET order_count = order_count + 1 WHERE id = NEW.user_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE users SET order_count = order_count - 1 WHERE id = OLD.user_id;
    END IF;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintain_order_count
AFTER INSERT OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION update_order_count();

Управление триггерами

-- Посмотреть все триггеры таблицы
SELECT trigger_name, event_manipulation, action_timing, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'users';

-- Временно отключить триггер
ALTER TABLE users DISABLE TRIGGER set_updated_at;

-- Включить обратно
ALTER TABLE users ENABLE TRIGGER set_updated_at;

-- Отключить все триггеры таблицы (нужны права суперпользователя)
ALTER TABLE users DISABLE TRIGGER ALL;

-- Удалить триггер
DROP TRIGGER IF EXISTS set_updated_at ON users;

Когда использовать триггеры, когда нет

Стоит использовать:

• updated_at — универсально
• Аудит изменений — триггер гарантирует запись даже от DBA
• Счётчики (denormalization) — если SELECT JOIN слишком медленный
• Бизнес-правила, которые ВСЕГДА должны соблюдаться

Не стоит:

• Сложная бизнес-логика — сложно тестировать и отлаживать
• Производительность — каждый триггер замедляет INSERT/UPDATE/DELETE
• Цепочки триггеров — триггер вызывает изменение → вызывает другой триггер → ...

Итог

Триггеры — мощный инструмент, но каждый добавляет скрытую сложность. Документируйте их, держите логику простой и тестируйте поведение при массовых операциях.