Триггеры в PostgreSQL: основы

Приветствую! Сегодня поговорим про триггеры в PostgreSQL.

Начнём с базы: триггер в PostgreSQL — это такая функция, которая запускается автоматически при определённом событии в таблице. С триггерами можно автоматизировать массу рутины и освободить приложение от сложных проверок и вычислений, но это палка о двух концах.

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

• Для поддержания целостности данных — скажем, обновление зависимых полей updated_at и т. д..

• Для сложных проверок — если стандартные CHECK и CONSTRAINT недостаточны.

• Для встроенной бизнес-логики — например, чтобы вести историю изменений.

Простой пример: обновление поля updated_at при изменении записи

Итак, начинаем с примера. У нас есть таблица orders, и мы хотим обновлять поле updated_at при каждом изменении записи.

CREATE TABLE orders (     order_id SERIAL PRIMARY KEY,     product_name VARCHAR(100),     quantity INT,     updated_at TIMESTAMP );  CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$ BEGIN     NEW.updated_at := NOW();     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER before_update_orders BEFORE UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION set_updated_at();

Просто и удобно. Но можно сделать ещё лучше, добавив проверку WHEN, чтобы обновлять updated_at только при изменении quantity. Экономим ресурсы — не будем гонять базу впустую.

WHEN и условия в триггера

Добавим проверку с помощью условия WHEN, чтобы обновление updated_at происходило только при реальном изменении quantity.

CREATE OR REPLACE FUNCTION conditional_update_timestamp() RETURNS TRIGGER AS $$ BEGIN     IF NEW.quantity IS DISTINCT FROM OLD.quantity THEN         NEW.updated_at := NOW();     END IF;     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER conditional_before_update BEFORE UPDATE ON orders FOR EACH ROW WHEN (OLD.quantity IS DISTINCT FROM NEW.quantity) EXECUTE FUNCTION conditional_update_timestamp();

Теперь триггер не выполняется зря.

BEFORE vs. AFTER триггеры: когда и зачем

BEFORE и AFTER — это не только про момент срабатывания. У них разные задачи:

• BEFORE триггеры идеальны для проверки и изменения данных перед сохранением.

• AFTER триггеры полезны для действий, которые зависят от конечного состояния данных (например, логирование).

Пример AFTER триггера для логирования изменений:

CREATE TABLE order_history (     history_id SERIAL PRIMARY KEY,     order_id INT,     changed_quantity INT,     change_time TIMESTAMP );  CREATE OR REPLACE FUNCTION log_order_changes() RETURNS TRIGGER AS $$ BEGIN     INSERT INTO order_history (order_id, changed_quantity, change_time)     VALUES (OLD.order_id, OLD.quantity, NOW());     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER after_update_log AFTER UPDATE ON orders FOR EACH ROW EXECUTE FUNCTION log_order_changes();

Теперь при каждом обновлении мы сохраняем историю изменений. Удобно.

INSTEAD OF триггеры: управление представлениями

INSTEAD OF триггеры полезны для views, позволяя управлять сложными операциями внутри них. Представим, что есть представление employee_salary, и нужно обновлять данные в оригинальной таблице через это представление:

CREATE VIEW employee_salary AS SELECT id, name, salary FROM employees;  CREATE OR REPLACE FUNCTION update_employee_salary() RETURNS TRIGGER AS $$ BEGIN     UPDATE employees SET salary = NEW.salary WHERE id = NEW.id;     RETURN NULL; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER instead_of_update_salary INSTEAD OF UPDATE ON employee_salary FOR EACH ROW EXECUTE FUNCTION update_employee_salary();

Теперь любые изменения в представлении employee_salary транслируются в таблицу employees.

Обработка ошибок в триггерах

Если триггерная функция завершится с ошибкой, PostgreSQL откатывает всю транзакцию. Но иногда нужно просто записать ошибку, не прерывая транзакцию. Для этого используем EXCEPTION.

CREATE OR REPLACE FUNCTION safe_update_timestamp() RETURNS TRIGGER AS $$ BEGIN     BEGIN         NEW.updated_at := NOW();     EXCEPTION         WHEN OTHERS THEN             INSERT INTO error_logs (error_message, error_time)              VALUES (SQLERRM, NOW());             RETURN OLD;     END;     RETURN NEW; END; $$ LANGUAGE plpgsql;

Если что-то пойдёт не так, триггер запишет ошибку в error_logs и продолжит выполнение.

Асинхронные триггеры и фоновые задачи

Для тяжёлых операций лучше вынести задачи в асинхронный процесс. PostgreSQL поддерживает асинхронность с помощью pg_notify, LISTEN и UNLISTEN.

CREATE OR REPLACE FUNCTION notify_log() RETURNS TRIGGER AS $$ BEGIN     PERFORM pg_notify('log_channel', json_build_object(         'id', NEW.id,         'action', TG_OP,         'time', NOW()     )::text);     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER after_update_notify AFTER UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION notify_log();

Этот триггер отправляет уведомление по каналу log_channel, и внешнее приложение может обрабатывать его асинхронно.

Триггеры для JSON данных

Сложные структуры данных вроде JSON открывают некоторые возможности для триггеров. Например, триггер может проверять или парсить содержимое JSON, выделяя данные в другие таблицы.

CREATE OR REPLACE FUNCTION validate_json_data() RETURNS TRIGGER AS $$ BEGIN     IF NOT (NEW.data ->> 'required_field' IS NOT NULL) THEN         RAISE EXCEPTION 'Required field is missing in JSON data';     END IF;     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER before_insert_json BEFORE INSERT ON my_table FOR EACH ROW EXECUTE FUNCTION validate_json_data();

Каскадные триггеры и как избежать циклов

Когда один триггер вызывает другой, можно случайно попасть в бесконечный цикл. Проверяйте такие ситуации с помощью TG_LEVEL или флагов:

IF TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW' THEN     -- Основная логика END IF;

Примеры использования всего этого дела

Записываем историю изменений

Допустим, есть у нас CRM-система, а клиент в один прекрасный момент начинает задавать вопросики — кто, мол, сменил его e-mail с рабочей почты на некий «lovefluffy72@gmail.com«? Ручками это не отследишь. Тут-то помогут наши триггеры, со своим блокнотом и карандашиком: они будут записывать каждое изменение, и мы навсегда избавимся от ночных звонков с вопросом «кто это сделал?».

CREATE TABLE customer_changes (     change_id SERIAL PRIMARY KEY,     customer_id INT,     old_name VARCHAR(255),     old_email VARCHAR(255),     changed_by VARCHAR(255),     change_time TIMESTAMP );  CREATE OR REPLACE FUNCTION log_customer_changes() RETURNS TRIGGER AS $$ BEGIN     INSERT INTO customer_changes (customer_id, old_name, old_email, changed_by, change_time)     VALUES (OLD.customer_id, OLD.name, OLD.email, current_user, NOW());     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER audit_customer_changes AFTER UPDATE ON customers FOR EACH ROW EXECUTE FUNCTION log_customer_changes();

Теперь вся история изменений лежит в таблице customer_changes, и всегда есть козырь в рукаве, чтобы ответить, кто, когда и зачем поменял данные.

Заказы

Допустим, есть интернет-магазин, где пользователи обновляют свои заказы по 10 раз на дню. Что произойдет, если кто-то забудет пересчитать общую стоимость заказа после изменения количества товара? Да, это будет ошибка на миллион (ну или хотя бы на хороший ужин). Чтобы этого избежать, триггер перехватывает момент и обновляет поле total_amount на лету. Все счастливы.

CREATE OR REPLACE FUNCTION update_order_total() RETURNS TRIGGER AS $$ BEGIN     UPDATE orders     SET total_amount = (         SELECT SUM(quantity * price)         FROM order_items         WHERE order_id = NEW.order_id     )     WHERE order_id = NEW.order_id;     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER update_order_total_trigger AFTER INSERT OR UPDATE OR DELETE ON order_items FOR EACH ROW EXECUTE FUNCTION update_order_total();

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

Проверка наличия товара на складе

А теперь представим страшный сценарий: пользователь оформляет заказ на 100500 единиц товара, а в наличии — две штуки. Триггер проверит, есть ли нужное количество товара, и если нет — сообщит.

CREATE OR REPLACE FUNCTION check_stock_availability() RETURNS TRIGGER AS $$ BEGIN     IF (SELECT quantity FROM inventory WHERE product_id = NEW.product_id) < NEW.quantity THEN         RAISE EXCEPTION 'Insufficient stock for product %', NEW.product_id;     END IF;     RETURN NEW; END; $$ LANGUAGE plpgsql;  CREATE TRIGGER before_order_item_insert BEFORE INSERT ON order_items FOR EACH ROW EXECUTE FUNCTION check_stock_availability();

Теперь триггер стал главным контролером на складе. Он не допустит дефицита и убережет нас от неприятных разговоров с клиентом.

Всегда ли нужен триггер?

В некоторых случаях использование триггеров можно заменить бизнес-логикой в приложении или функциями базы данных. Например:

• Бизнес-логика в коде: если логика не зависит от целостности базы, лучше обрабатывать обновления и логирование на уровне приложения.

• Встроенные функции PostgreSQL: например, для проверки условий иногда достаточно встроенных механизмов базы данных, например как CHECK CONSTRAINT, вместо создания отдельной триггерной функции.

Спасибо за внимание. Делитесь своими кейсами использования триггеров в комментариях!

---

Пользуясь случаем, напоминаю про ближайшие открытые уроки по PostgreSQL:

• 13 ноября: «Ты — индекс в Postgres, Я — индекс в ClickHouse. Мы разные». Узнать подробнее

• 27 ноября: «Обзор основных различий между Oracle и PostgreSQL». Узнать подробнее