Триггеры PostgreSQL

Триггер PostgreSQL — это хранимая процедура, которая автоматически выполняется (срабатывает) в базе данных при наступлении определённого события, связанного с таблицей или представлением (view). Триггеры являются частью системы управления реляционными базами данных PostgreSQL и используются для обеспечения целостности данных, аудита изменений, реализации сложной бизнес-логики и автоматизации рутинных операций.

Общая характеристика

Триггеры в PostgreSQL относятся к категории серверных объектов, которые не вызываются явно пользователем или приложением, а запускаются автоматически при выполнении операций модификации данных (INSERT, UPDATE, DELETE) или, в некоторых случаях, при выполнении определённых команд DDL (TRUNCATE). Основное отличие триггеров PostgreSQL от триггеров в других СУБД (например, MySQL или Oracle) заключается в их тесной интеграции с языком программирования PL/pgSQL и поддержке событийного механизма, позволяющего определять момент срабатывания (до или после события) и область действия (для каждой строки или для всей команды).

История

Механизм триггеров появился в PostgreSQL на ранних этапах развития, однако его современная реализация была значительно расширена в версии 9.0 (2010 год), когда была добавлена поддержка триггеров на уровне команд (statement-level triggers) и возможность использования триггеров для представлений (instead-of triggers). В версии 9.1 появилась поддержка триггеров для событий DDL (например, CREATE TABLE). В версии 9.3 были добавлены триггеры для событий TRUNCATE. Дальнейшие улучшения в версиях 10 и 11 касались оптимизации производительности и расширения возможностей языка PL/pgSQL. В версии 14 (2021 год) были добавлены триггеры для событий MERGE. Начиная с версии 15, PostgreSQL поддерживает триггеры, написанные на любом языке, поддерживаемом сервером (PL/pgSQL, PL/Python, PL/Perl, PL/Tcl и других).

Классификация триггеров

Триггеры PostgreSQL классифицируются по нескольким признакам.

По моменту срабатывания относительно события

• BEFORE (до события) — триггер выполняется до того, как операция модификации данных будет применена к таблице. Используется для проверки или модификации вставляемых/обновляемых данных, а также для отмены операции (с помощью RAISE EXCEPTION).
• AFTER (после события) — триггер выполняется после того, как операция модификации данных завершена. Используется для аудита, синхронизации с другими таблицами, рассылки уведомлений.
• INSTEAD OF (вместо события) — применяется только для представлений (views). Позволяет заменить стандартную операцию модификации на пользовательскую логику, что необходимо для обновляемых представлений.

По уровню срабатывания

• Row-level triggers (для каждой строки) — срабатывают один раз для каждой строки, затронутой операцией. Например, при UPDATE, затрагивающем 100 строк, триггер выполнится 100 раз.
• Statement-level triggers (для всей команды) — срабатывают один раз для всей команды SQL, независимо от количества затронутых строк. Например, при UPDATE, затрагивающем 100 строк, триггер выполнится 1 раз.

По типу события

• INSERT — срабатывает при вставке новой строки.
• UPDATE — срабатывает при обновлении существующей строки.
• DELETE — срабатывает при удалении строки.
• TRUNCATE — срабатывает при выполнении команды TRUNCATE (доступно только для AFTER-триггеров на уровне команд).
• MERGE — срабатывает при выполнении команды MERGE (доступно с версии PostgreSQL 14).

По языку реализации

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

• PL/pgSQL — наиболее распространённый язык, встроенный в PostgreSQL.
• PL/Python — с использованием Python.
• PL/Perl — с использованием Perl.
• PL/Tcl — с использованием Tcl.
• C — для написания высокопроизводительных триггеров (требуется компиляция).

Устройство и синтаксис

Триггер в PostgreSQL создаётся с помощью команды CREATE TRIGGER. Общий синтаксис:

``sql CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER имя_триггера { BEFORE | AFTER | INSTEAD OF } { событие [ OR ... ] } ON имя_таблицы_или_представления [ FROM ссылочная_таблица ] [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ] [ REFERENCING { { OLD | NEW } TABLE [ AS ] имя_таблицы } [...] ] [ FOR [ EACH ] { ROW | STATEMENT } ] [ WHEN ( условие ) ] EXECUTE FUNCTION имя_функции ( аргументы ) ``

Ключевые элементы:

• имя_триггера — уникальное в пределах таблицы имя.
• событие — INSERT, UPDATE, DELETE, TRUNCATE или MERGE. Для UPDATE можно указать конкретные столбцы: UPDATE OF столбец1, столбец2.
• FOR EACH ROW или FOR EACH STATEMENT — определяет уровень срабатывания.
• WHEN (условие) — необязательное условие, при котором триггер выполняется. Условие проверяется для каждой строки (для row-level) или для команды (для statement-level).
• EXECUTE FUNCTION — указывает функцию, которая будет выполнена. Функция должна быть определена заранее.

Функция триггера должна возвращать тип trigger (или event_trigger для DDL-триггеров). Внутри функции доступны специальные переменные:

• NEW — новая строка (для INSERT и UPDATE).
• OLD — старая строка (для UPDATE и DELETE).
• TG_OP — тип операции (INSERT, UPDATE, DELETE, TRUNCATE).
• TG_TABLE_NAME — имя таблицы.
• TG_WHEN — момент срабатывания (BEFORE, AFTER, INSTEAD OF).
• TG_LEVEL — уровень (ROW, STATEMENT).

Применение

Обеспечение целостности данных

Триггеры используются для проверки и корректировки данных перед их сохранением. Например, триггер BEFORE INSERT может проверять, что значение поля «возраст» не превышает 150 лет, и в противном случае вызывать исключение.

Аудит изменений

Триггеры AFTER INSERT/UPDATE/DELETE позволяют записывать все изменения в отдельную таблицу аудита. Например, при каждом изменении записи в таблице «сотрудники» триггер может сохранять старые и новые значения, имя пользователя и время изменения.

Автоматическое обновление связанных данных

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

Реализация сложной бизнес-логики

Триггеры позволяют реализовать правила, которые сложно или невозможно реализовать с помощью ограничений (constraints). Например, триггер может проверять, что у одного клиента не может быть более 10 активных заказов.

Синхронизация с внешними системами

Триггеры могут отправлять уведомления через механизм NOTIFY или вызывать внешние процедуры через расширения (например, pg_notify).

Пример

Рассмотрим простой триггер на PL/pgSQL, который автоматически проставляет временную метку при вставке новой строки в таблицу.

```sql -- Создание таблицы CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMP );

-- Создание функции триггера CREATE OR REPLACE FUNCTION set_created_at() RETURNS TRIGGER AS $$ BEGIN NEW.created_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

-- Создание триггера CREATE TRIGGER trg_set_created_at BEFORE INSERT ON employees FOR EACH ROW EXECUTE FUNCTION set_created_at(); ```

Теперь при вставке строки в таблицу employees поле created_at будет автоматически заполняться текущей датой и временем.

Ограничения и особенности

• Производительность: Триггеры, особенно row-level, могут существенно замедлять операции массовой вставки или обновления. Рекомендуется минимизировать их использование в высоконагруженных системах.
• Отладка: Триггеры сложно отлаживать, так как они выполняются на стороне сервера. Для отладки можно использовать RAISE NOTICE или внешние инструменты.
• Каскадные триггеры: Триггеры могут вызывать другие триггеры, что может привести к рекурсии. PostgreSQL имеет механизм защиты от бесконечной рекурсии (ограничение на глубину вложенности).
• Транзакционность: Триггеры выполняются в рамках той же транзакции, что и вызвавшая их команда. Если триггер вызывает исключение, вся транзакция откатывается.
• Порядок выполнения: Если на одну таблицу и одно событие назначено несколько триггеров, они выполняются в алфавитном порядке имён.

Сравнение с другими СУБД

В отличие от MySQL, где триггеры поддерживаются только для таблиц и не могут быть написаны на языках, отличных от SQL, PostgreSQL предоставляет гораздо более гибкий механизм. В Oracle триггеры имеют схожую функциональность, но отличаются синтаксисом и возможностями (например, поддержка триггеров на уровне схемы). В Microsoft SQL Server триггеры также поддерживаются, но их синтаксис отличается (используется язык T-SQL).

Источники

• PostgreSQL Documentation. Chapter 39. Triggers.
• PostgreSQL Documentation. CREATE TRIGGER.
• PostgreSQL Documentation. PL/pgSQL — Triggers.
• PostgreSQL Documentation. Event Triggers.
• PostgreSQL Documentation. Rules vs Triggers.
• PostgreSQL Documentation. MERGE (с версии 14).
• PostgreSQL Documentation. NOTIFY.
• PostgreSQL Documentation. PL/pgSQL — Trigger Functions.