Menu

Триггеры в PostgreSQL: CREATE TRIGGER на примерах

Триггер автоматически вызывает функцию, когда строки вставляются, обновляются или удаляются. В PostgreSQL сначала пишут триггерную функцию, которая возвращает trigger, а потом привязывают её к таблице через CREATE TRIGGER.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

Триггер заставляет PostgreSQL автоматически вызывать функцию, когда таблица меняется. Сначала напишите триггерную функцию, объявленную как RETURNS trigger, затем привяжите её: CREATE TRIGGER name BEFORE UPDATE ON table FOR EACH ROW EXECUTE FUNCTION fn();. Классический пример: поддерживать актуальным столбец updated_at:

Обновлён был только пост 1, поэтому метка времени есть только у него. NEW это строка, которая сейчас будет записана; триггер BEFORE может её изменить и обязан её вернуть. Одну и ту же функцию можно привязать к каждой таблице, где есть столбец updated_at.

BEFORE, AFTER и INSTEAD OF

МоментКогда выполняетсяТипичное применение
BEFOREдо записи строкиизменить или проверить NEW, или пропустить строку
AFTERпосле записи строкизаписать в другие таблицы: журналы аудита, счётчики
INSTEAD OFвместо изменения, только для представленийсделать представление изменяемым

FOR EACH ROW вызывает функцию по разу на каждую затронутую строку. FOR EACH STATEMENT (по умолчанию) вызывает её один раз на оператор, даже если ни одна строка не изменилась. В строковом триггере NEW содержит новую строку (INSERT, UPDATE), а OLD предыдущую (UPDATE, DELETE). TG_OP называет операцию, которая вызвала триггер, а TG_TABLE_NAME таблицу.

Значение, которое возвращает строковый триггер BEFORE, определяет, что будет дальше: верните NEW (изменённую или нет), чтобы продолжить, или NULL, чтобы пропустить строку без ошибки. Возвращаемое значение триггера AFTER игнорируется, поэтому по соглашению он возвращает NULL.

Журнал аудита на триггере AFTER

Таблица аудита записывает два INSERT, UPDATE с 10 на 12 и DELETE товара по цене 20. При вставке OLD равен NULL, а при удалении NULL равен NEW, отсюда coalesce. Чтобы хранить строки целиком, кладите to_jsonb(OLD) и to_jsonb(NEW) в столбец jsonb.

Запретить или пропустить изменение

Триггер BEFORE может отклонить изменение через RAISE EXCEPTION, и тогда оператор прервётся, или пропустить строку, вернув NULL:

DELETE удалил черновик и молча оставил оплаченный счёт. Триггер BEFORE DELETE возвращает OLD, чтобы разрешить удаление. Если хотите, чтобы падал весь оператор, замените RETURN NULL на RAISE EXCEPTION 'invoice % is paid', OLD.id;. Ошибку отладить проще, чем строку, которая тихо осталась на месте.

Срабатывание только при изменении столбца

UPDATE OF column ограничивает триггер обновлениями, которые задают этот столбец. Условие WHEN пропускает вызов функции, если оно ложно. IS DISTINCT FROM считает NULL обычным значением, поэтому ловит и изменения на NULL или с NULL:

Первое обновление ставит ту же цену, поэтому WHEN ложно и счётчик остаётся равным 0. Второе её меняет, и price_changed в итоге равно 1.

Список, отключение и удаление триггеров

-- triggers you created (excludes the ones behind foreign keys)
SELECT tgrelid::regclass AS table_name, tgname
FROM pg_trigger WHERE NOT tgisinternal;

ALTER TABLE products DISABLE TRIGGER products_price;
ALTER TABLE products ENABLE TRIGGER products_price;
DROP TRIGGER products_price ON products;

В psql \d products показывает триггеры таблицы. CREATE OR REPLACE TRIGGER есть с PostgreSQL 14, а EXECUTE FUNCTION с PostgreSQL 11; в старом коде пишут EXECUTE PROCEDURE, и это по-прежнему работает. Триггер, который пишет в свою же таблицу, может снова вызвать сам себя, так что защищайтесь от зацикливания (pg_trigger_depth() возвращает уровень вложенности). В главе о триггерных функциях перечислены все переменные TG_, а сам PL/pgSQL разобран на странице о функциях и процедурах.

Часто задаваемые вопросы

Может ли триггер PostgreSQL работать без функции?

Нет. CREATE TRIGGER всегда вызывает функцию, объявленную как RETURNS trigger. Сначала напишите функцию, обычно на PL/pgSQL, потом привяжите её.

В каком порядке срабатывают триггеры одной таблицы?

Триггеры одного вида на одно и то же событие срабатывают в алфавитном порядке имён. Если порядок важен, начинайте имена с цифр или букв.

Откатывает ли ошибка в триггере оператор?

Да. Ошибка, выброшенная в триггере, например через RAISE EXCEPTION, прерывает оператор, который его вызвал, и всю транзакцию, если не перехватить ошибку точкой сохранения.

Срабатывают ли триггеры на TRUNCATE?

Строковые триггеры DELETE не срабатывают. Чтобы реагировать на него, создайте триггер уровня оператора с BEFORE TRUNCATE или AFTER TRUNCATE.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать