Приветствую! Сегодня поговорим про триггеры в 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». Узнать подробнее