Триггер - это код, который PostgreSQL запускает сам, без вашего явного вызова, в ответ на изменение данных или на команду. Вы один раз описываете правило ("при каждом UPDATE цены пиши строку в журнал"), и дальше база соблюдает его за вас, кто бы ни выполнял запрос - приложение, отчёт, ручной psql или внешняя интеграция вроде postgresql для 1с. В этом уроке разберём, из чего состоит триггер, чем отличаются BEFORE/AFTER/INSTEAD OF и ROW/STATEMENT, что лежит в записях NEW и OLD, где триггеры реально полезны (аудит, денормализация, проверки), что такое событийные триггеры на DDL и почему триггеры легко превращаются в тормоз.
Как это работает
Триггер в PostgreSQL - это связка двух сущностей. Сначала вы пишете триггерную функцию: обычную функцию на PL/pgSQL (или другом языке) с типом возврата trigger. Затем командой CREATE TRIGGER привязываете её к таблице и говорите, на какое событие и в какой момент её звать. Одну функцию можно повесить на несколько таблиц - внутри она узнает контекст через служебные переменные.
Момент срабатывания задаёт суть. BEFORE-триггер выполняется до того, как строка реально записана, поэтому в нём можно поправить или отменить изменение. AFTER-триггер срабатывает после записи, когда новое состояние уже зафиксировано в рамках транзакции, - это место для аудита и каскадных действий. INSTEAD OF существует только для представлений (VIEW): обычная вьюха не принимает запись напрямую, а такой триггер перехватывает INSERT/UPDATE/DELETE и раскладывает их по настоящим таблицам.
Второе важное деление - уровень. Строчный триггер (FOR EACH ROW) вызывается на каждую затронутую строку: обновили 5000 строк - функция отработает 5000 раз. Операторный (FOR EACH STATEMENT) срабатывает ровно один раз на всю команду, даже если она не задела ни одной строки. Строчный нужен, когда логика зависит от конкретных значений; операторный - когда хватает факта "команда была".
Внутри строчной функции доступны NEW и OLD - записи типа таблицы. На INSERT есть только NEW (новая строка), на DELETE - только OLD (удаляемая), на UPDATE - обе. В BEFORE-триггере можно менять поля NEW, и эти правки попадут в таблицу. Возврат тоже значим: BEFORE ROW должен вернуть NEW (или изменённую запись), чтобы операция продолжилась, либо NULL - чтобы тихо отменить её для этой строки. У AFTER-триггера возвращаемое значение игнорируется. Контекст подскажут переменные TG_OP (INSERT/UPDATE/DELETE), TG_TABLE_NAME, TG_WHEN и другие.
Событийные триггеры (event triggers) - отдельный механизм, реагирующий не на данные, а на сами команды DDL: создание таблицы, ALTER, DROP. Они ловятся на события ddl_command_start, ddl_command_end, sql_drop и table_rewrite и помогают вести аудит изменений схемы или запрещать опасные операции на проде.
SQL и примеры
Аудит изменения цены билета в схеме bookings. Сначала журнал и триггерная функция, которая пишет в него старую и новую сумму:
Код: Выделить всё
CREATE TABLE bookings.fare_audit (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
ticket_no char(13),
flight_id integer,
old_amount numeric(10,2),
new_amount numeric(10,2),
changed_by text DEFAULT current_user,
changed_at timestamptz DEFAULT now()
);
CREATE OR REPLACE FUNCTION bookings.log_fare_change()
RETURNS trigger AS $$
BEGIN
IF NEW.amount IS DISTINCT FROM OLD.amount THEN
INSERT INTO bookings.fare_audit
(ticket_no, flight_id, old_amount, new_amount)
VALUES (OLD.ticket_no, OLD.flight_id, OLD.amount, NEW.amount);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
Код: Выделить всё
CREATE TRIGGER trg_fare_audit
AFTER UPDATE OF amount ON bookings.ticket_flights
FOR EACH ROW
EXECUTE FUNCTION bookings.log_fare_change();
Проверка-валидация в BEFORE-триггере: запретим отрицательную сумму, не доводя дело до записи:
Код: Выделить всё
CREATE OR REPLACE FUNCTION bookings.check_amount()
RETURNS trigger AS $$
BEGIN
IF NEW.amount < 0 THEN
RAISE EXCEPTION 'Сумма не может быть отрицательной: %', NEW.amount;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_check_amount
BEFORE INSERT OR UPDATE ON bookings.ticket_flights
FOR EACH ROW
EXECUTE FUNCTION bookings.check_amount();
Код: Выделить всё
CREATE OR REPLACE FUNCTION bookings.count_flight_updates()
RETURNS trigger AS $$
DECLARE n bigint;
BEGIN
SELECT count(*) INTO n FROM changed;
RAISE NOTICE 'Обновлено рейсов: %', n;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_flights_bulk
AFTER UPDATE ON bookings.flights
REFERENCING NEW TABLE AS changed
FOR EACH STATEMENT
EXECUTE FUNCTION bookings.count_flight_updates();
Код: Выделить всё
CREATE OR REPLACE FUNCTION guard_drops()
RETURNS event_trigger AS $$
BEGIN
RAISE EXCEPTION 'DROP запрещён на этом сервере';
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER no_drop ON sql_drop
EXECUTE FUNCTION guard_drops();
- Изменять NEW в AFTER-триггере бессмысленно: строка уже записана, правки не применятся. Меняйте только в BEFORE ROW.
- Забыть RETURN NEW в BEFORE ROW - и операция тихо отменится (вернётся NULL), строка не сохранится, ошибки не будет.
- Тяжёлая логика в FOR EACH ROW на пакетном UPDATE: функция вызывается на каждую строку. На загрузке миллионов строк это умножает время в разы - берите FOR EACH STATEMENT с transition tables.
- Триггер, который UPDATE-ит ту же таблицу, способен запустить сам себя - рекурсия. Защищайтесь условием выхода или pg_trigger_depth().
- Порядок строчных триггеров одного события - по алфавиту имён, а не по дате создания. Называйте осознанно, если порядок важен.
- Триггеры не видны в EXPLAIN плана, но их время показывает EXPLAIN ANALYZE отдельной строкой Trigger ... time - проверяйте её при разборе медленных вставок.
- По умолчанию триггеры не срабатывают на действия от внешних ключей и на COPY-логику репликации; для логической репликации нужен ENABLE ALWAYS/REPLICA TRIGGER.
- Создайте таблицу аудита и триггерную функцию из примера, привяжите AFTER UPDATE OF amount к bookings.ticket_flights.
- Выполните UPDATE bookings.ticket_flights SET amount = amount + 100 WHERE ticket_no = (SELECT ticket_no FROM bookings.ticket_flights LIMIT 1) и проверьте, что в fare_audit появилась запись.
- Сделайте UPDATE того же билета без смены суммы (например SET fare_conditions = fare_conditions) и убедитесь, что новой строки в аудите нет.
- Добавьте BEFORE-триггер проверки и попробуйте записать отрицательную сумму - поймайте RAISE EXCEPTION.
- Оберните UPDATE и проверку в одну транзакцию, сделайте ROLLBACK и убедитесь, что аудит откатился вместе с данными.
- Через EXPLAIN ANALYZE на UPDATE найдите строку Trigger и оцените, сколько времени съел триггер.
- Временно отключите триггер через ALTER TABLE ... DISABLE TRIGGER и проверьте, что аудит перестал писаться.
- Чем BEFORE-триггер принципиально отличается от AFTER, и в каком из них можно отменить или изменить строку?
- Что лежит в NEW и OLD при INSERT, UPDATE и DELETE и какое значение должна вернуть BEFORE ROW функция?
- Когда выгоднее FOR EACH STATEMENT вместо FOR EACH ROW и как тогда увидеть изменённые строки?
- Почему IS DISTINCT FROM надёжнее обычного сравнения при отслеживании изменений столбца?
- Зачем нужны event triggers и на какие события DDL их можно повесить?
- Как триггер может уйти в рекурсию и какими способами это предотвратить?
