Триггеры и хранимые процедуры — когда НЕ надо

Опирается на правила: PG-TR-001 … PG-TR-095 из PostgreSQL Style Guide → раздел Триггеры и хранимые процедуры.

Важно знать

Бизнес-логика живёт в коде, не в БД. В коде тестируется, видна в IDE, версионируется в git.

Триггер для updated_at = now() — антипаттерн. DEFAULT now() + явный UPDATE.

Денормализация триггером — терпимо, но MV или event-handler гибче.

Audit-trail триггером — оправдан для compliance (банки, медицина).

Валидация: простая через CHECK, сложная — код с SELECT FOR UPDATE.

Stored procedures почти никогда не нужны — @Transactional + jOOQ batch.

FOR EACH ROW дорог на bulk — 1M строк = 1M вызовов.

Триггеры могут вызывать deadlock — порядок локов недетерминирован.

Бизнес-логика в БД — антипаттерн в современной разработке. UCP формулирует когда (редко) триггеры всё же оправданы.

Базовая позиция

PG-TR-001..003: логика в коде, не в БД.

	В коде	В триггерах
Тестирование	unit-тесты	интеграция с БД
Видимость	IDE с навигацией	отдельный SQL
Версионирование	git вместе с фичей	миграции (отдельный релиз)
Откат	релиз приложения	миграция (часто болезненно)
Привязка к движку	нет	да (plpgsql переписать сложно)

Это не значит «триггеры зло» — есть редкие сценарии (см. ниже). Но default — без них.

Что обычно делают триггерами и почему НЕ надо

updated_at

PG-TR-010..011: антипаттерн.

-- ✗ — триггер
CREATE TRIGGER tr_set_updated_at
BEFORE UPDATE ON order_doc FOR EACH ROW EXECUTE FUNCTION set_updated_at();

-- ✓ — DDL + явный UPDATE
CREATE TABLE order_doc (
    ...
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

-- в коде
UPDATE order_doc SET status = ?, updated_at = now() WHERE id = ?;

Триггер скрывает поведение — разработчик пишет UPDATE без updated_at, ожидает старое, а триггер тихо обновил.

Денормализация (счётчики)

PG-TR-020: терпимо, но не идеально.

-- терпимо
CREATE TRIGGER tr_update_post_count
AFTER INSERT OR DELETE ON post
FOR EACH ROW EXECUTE FUNCTION update_forum_post_count();

Альтернативы:

Materialized view — проще, видно явно.
Read Model в коде через event-handler — гибче.
Расчёт on-the-fly при чтении — если данных мало.

Триггер удобен для мгновенной консистентности (счётчик точный после COMMIT), цена — невидимая логика.

Audit-trail

PG-TR-030: оправдан для compliance.

CREATE TRIGGER tr_order_doc_audit
AFTER INSERT OR UPDATE OR DELETE ON order_doc
FOR EACH ROW EXECUTE FUNCTION log_to_audit();

Альтернативы:

Outbox + код — каждое изменение явно создаёт audit-event. Гибче, можно забыть.
Logical replication / CDC — внешний сервис ловит WAL.
Триггер — гарантия покрытия, не зависит от разработчика.

Для compliance-чувствительных (банки, медицина) — разумный выбор.

Валидация инвариантов

PG-TR-040..041: CHECK > триггер.

-- ✓ — простые
ALTER TABLE order_doc ADD CONSTRAINT ck_order_total_positive CHECK (total_amount >= 0);
-- EXCLUDE для непересечения интервалов

-- терпимо — сложный инвариант
CREATE TRIGGER tr_doctor_on_call_check
BEFORE UPDATE ON doctor FOR EACH ROW EXECUTE FUNCTION check_at_least_one_oncall();

Если инвариант между несколькими строками или таблицами — лучше код с FOR UPDATE + SELECT count.

Когда триггер оправдан

PG-TR-050:

Compliance «не пропустить ни одно изменение» (медицина, банк, регулируемая отрасль).
PG как «источник истины» для нескольких приложений.
Денормализация с гарантией мгновенной консистентности.
Защита от ручного UPDATE через psql (devops).

Хранимые процедуры

PG-TR-060..063: почти никогда.

Что обычно делают процедурами:

Сложная транзакция → @Transactional в Spring.
Bulk-логика → INSERT INTO ... SELECT без процедуры.
Производительность → редко выигрывает у jOOQ batch.

Оправдано:

Очень тяжёлые SQL, где network round-trip существенен (миллионы строк, сложные JOIN).
ETL: читает гигабайты, агрегирует, пишет.

PG procedures (PG11+) умеют COMMIT/ROLLBACK внутри (в отличие от functions):

CREATE PROCEDURE archive_old_orders() LANGUAGE plpgsql AS $$
DECLARE rows_affected integer := 1;
BEGIN
    WHILE rows_affected > 0 LOOP
        WITH deleted AS (
            DELETE FROM order_doc
            WHERE created_at < now() - interval '1 year'
            RETURNING *
        )
        INSERT INTO order_archive SELECT * FROM deleted;
        GET DIAGNOSTICS rows_affected = ROW_COUNT;
        COMMIT;
    END LOOP;
END;
$$;

Альтернатива — @Scheduled + батчированный код в Java. Та же логика, тестируется, видно в репо.

Стоимость

PG-TR-070..072:

BEFORE — выполняется до записи, может изменить значения.
AFTER — выполняется после, может только читать.
FOR EACH ROW — на каждую строку (медленно на bulk).
FOR EACH STATEMENT — один раз на statement (быстрее).

На bulk FOR EACH ROW особенно дорог — INSERT INTO target SELECT FROM source 1M строк = 1M вызовов триггера.

Триггеры могут вызвать deadlock — если триггер обновляет другую таблицу, порядок локов меняется недетерминированно.

Дебаг

PG-TR-080..082:

-- найти триггеры на таблице
SELECT trigger_name, event_manipulation, action_statement
FROM information_schema.triggers
WHERE event_object_table = 'order_doc';

-- тело функции
SELECT prosrc FROM pg_proc WHERE proname = 'set_updated_at';

Лог в триггере:

CREATE OR REPLACE FUNCTION debug_trigger()
RETURNS trigger AS $$
BEGIN
    RAISE NOTICE 'TRIGGER fired: % on table %', TG_OP, TG_TABLE_NAME;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Тесты — через Testcontainers PostgreSQL.

Что запрещено

Антипаттерн	Правило	Что взамен
Триггер для `updated_at`	`PG-TR-090`	`DEFAULT now()` + явный UPDATE
Триггер для бизнес-валидации (`if total < 1000`)	`PG-TR-091`	Java-сервис
Триггер с `NOTIFY` или HTTP-вызовом	`PG-TR-092`	outbox + код
Цепочка триггеров A → B → C	`PG-TR-093`	event handler в коде
Stored procedure для логики из кода	`PG-TR-094`	`@Transactional` + Java
`BEFORE INSERT` модифицирует `NEW.*` без документирования	`PG-TR-095`	явно или DEFAULT
`FOR EACH ROW` на bulk операциях	`PG-TR-071`	`FOR EACH STATEMENT`

Куда дальше

PG → Триггеры — нормативные формулировки.
Spring @Transactional — логика в коде.
Materialized views — альтернатива денормализации.
Массивы и range — EXCLUDE constraint вместо триггера.
Auth → audit admin-команд — outbox для audit.
CQRS → read-model — event-driven денормализация.