Триггеры в 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:
