Как работать с триггерами в PostgreSQL
В этом тексте мы разберем, какие бывают триггеры и как их правильно создавать.
Что такое триггер базы данных
Триггер — действия, выполняемые при наступлении определенного события. В случае с SQL это может быть выполнение какой-либо операции.
В PostgreSQL триггеры можно использовать с таблицами, представлениями или сторонними таблицами.
Пример триггера. CREATE TRIGGER
Базовый синтаксис создания триггера:
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 | PROCEDURE } <имя_функции> ( <аргументы> )
Где событием может быть:
INSERT
UPDATE [ OF <имя_столбца> [, ... ] ]
DELETE
TRUNCATE
Типы триггеров
Событием, при котором срабатывает триггер, может быть выполнение команды INSERT, UPDATE или DELETE. Триггерная функция выполняется:
- до команды (BEFORE) ;
- после команды (AFTER);
- вместо команды (INSTEAD OF).
Также триггер может вызывать функцию:
- для каждой строки (FOR EACH ROW), затронутой вызвавшей триггер командой;
- один раз для оператора (FOR EACH STATEMENT) вне зависимости от количества строк, которые затрагивает команда. В том числе, если ни одна строка не затронута.
Чаще всего триггеры делят на следующие типы:
- выполняемые для каждой строки;
- выполняемые для операции — в этом случае триггерная функция будет выполнена вне зависимости от того, затрагивает ли операция хоть одну строку.
Триггерная функция
В PostgreSQL срабатывание триггера инициирует выполнение триггерной функции — ее нужно создать до самого триггера. Можно использовать большинство процедурных языков, вплоть до С. А вот функции на чистом SQL не поддерживаются (хотя можно создать на PL/pgSQL).
Базовый синтаксис создания триггерной функции
CREATE FUNCTION <имя_функции>()
RETURNS trigger AS
$$
BEGIN
<тело функции>
RETURN [NEW|OLD|NULL]
END;
$$
LANGUAGE plpgsql;
Триггерная функция должна возвращать тип TRIGGER и объявляться без аргументов. Внутри нее можно использовать переменные NEW, OLD и другие — полный список вы найдете в конце инструкции.
Переменная NEW хранит строку после выполнения операции, а OLD — до.
Триггерные функции, вызываемые для операторов (FOR EACH STATEMENT), должны возвращать NULL.
Функция, вызываемая для строки, может возвращать следующие значения: NULL, OLD или NEW.
RETURN NULL:
- для триггерных функций, выполняемых после операции (AFTER), возвращаемые значения игнорируются;
- если функция, выполняемая до операции со строкой (BEFORE), возвращает NULL, то такая строка пропускается. Операция по изменению, обновлению или удалению с ней не выполняется. При этом выполнение транзакции не прерывается.
RETURN OLD. Триггерные функции, выполняемые до (BEFORE) операции DELETE, должны возвращать исходную строку (OLD), если операцию удаления со строкой нужно выполнить. Если же ее нужно пропустить, возвращается NULL.
RETURN NEW. Функции, выполняемые до (BEFORE) операции INSERT или UPDATE, должны возвращать новую или обновленную строку, которая будет добавлена в таблицу.
Пример создания триггерной функции:
CREATE FUNCTION salary_check() RETURNS trigger AS $salary_check$
BEGIN
IF NEW.salary IS NULL THEN
RAISE EXCEPTION '% Нужно указать зарплату', NEW.name;
END IF;
IF NEW.salary < 0 THEN
RAISE EXCEPTION '% Зарплата должна быть больше нуля', NEW.name;
END IF;
RETURN NEW;
END;
$salary_check$ LANGUAGE plpgsql;
Для триггеров, выполняемых до операций INSERT или UPDATE для строки, удобно внутри триггерной функции работать с переменной NEW. Прямо в ней производить изменения и по выполнении возвращать измененную строку NEW. Если функция не изменяет результат операции со строкой, то нужно вернуть NEW.
Аргументы, указываемые для функции при создании триггера, позволяют разным триггерам вызывать одну и ту же функцию.
Базовый пример создания триггера PostgreSQL
Пример создания триггера, который вызывает для каждой изменяемой строки таблицы personal_salary триггерную функцию salary_check:
CREATE TRIGGER salary_check BEFORE UPDATE ON personal_salary
FOR EACH ROW EXECUTE FUNCTION salary_check();
Замена (REPLACE) или изменение триггера
Для изменения триггера используется операция REPLACE. Можно сразу указать CREATE OR REPLACE TRIGGER — в таком случае, если триггер не существует, он появится, а если существует — изменится:
CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OR INSERT ON personal_salary
FOR EACH ROW EXECUTE FUNCTION salary_check();
Условия срабатывания триггера
Срабатывание триггера UPDATE при изменении в определенном поле (ON), например salary:
CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF salary OR INSERT ON personal_salary
FOR EACH ROW EXECUTE FUNCTION salary_check();
Условия WHEN
- Для триггеров BEFORE условие вычисляется до выполнения функции и почти всегда равнозначно использованию WHEN в начале триггерной функции.
- Для AFTER выполнение триггера зависит от условий: будет ли поставлено задание в очередь, а также станет ли значение строки храниться до окончания выполнения операции. Использование WHEN помогает ускорить работу операторов, обрабатывающих большое количество строк, когда триггер должен сработать только для нескольких.
- В триггерах, выполняемых вместо операции (INSTEAD OF), условия WHEN не поддерживаются.
В условии можно использовать строки NEW и OLD. Например, этот триггер будет запускать функцию, только когда значение в поле salary изменилось:
CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF salary ON personal_salary
FOR EACH ROW WHEN (OLD.salary IS DISTINCT FROM NEW.salary) EXECUTE FUNCTION salary_check();
Триггеры для представлений
У триггеров, создаваемых для представлений, есть ряд особенностей.
- Срабатывающие до (BEFORE) или после (AFTER) команды, могут создаваться только для оператора (FOR EACH STATEMENT).
- Срабатывающие вместо (INSTEAD OF) операции могут быть заданы только для уровня строк (FOR EACH ROW).
Переименование триггера (ALTER)
Для переименования можно использовать команду ALTER TRIGGER. Изменим salary_check в salary_check_new:
ALTER TRIGGER salary_check ON personal_salary RENAME TO salary_check_new;
Удаление триггера (DROP)
Для удаления используется операция DROP TRIGGER. Ее синтаксис:
DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]
Удалим salary_check_new:
DROP TRIGGER salary_check_new ON personal_salary;
Разберем этот синтаксис подробнее.
- IF EXISTS — обычно, если триггер не существует, СУБД выдает ошибку при удалении. IF EXISTS позволяет отключить отображении этой ошибки.
- CASCADE — каскадное удаление триггера и всех зависимых объектов.
- RESTRICT — поведение, обратное каскадному: не удалять триггер, если существуют зависимые объекты. Это поведение по умолчанию.
Получение информации о существующих триггерах
Получить подобную информацию можно несколькими способами. Данные о триггерах хранятся в таблице information_schema.triggers.
Простой вариант — список триггеров отображается в описании таблицы:
\dS <table_name>
После описания полей таблицы будут указаны триггеры:
\dS personal_salary
Table "public.personal_salary"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
name | text | | |
salary | integer | | |
Triggers:
salary_check BEFORE INSERT OR UPDATE OF salary ON personal_salary FOR EACH ROW EXECUTE FUNCTION salary_check()
Для просмотра информации обо всех существующих триггерах можно воспользоваться системным каталогом pg_trigger, но можно получить и список триггеров для таблицы:
SELECT
tgname AS trigger_name
FROM
pg_trigger
WHERE
tgrelid = 'personal_salary'::regclass
ORDER BY
trigger_name;
Также просматривать триггеры можно через pgAdmin — популярный визуальный инструмент для работы с СУБД PostgreSQL. Нужные нам сведения выводятся в дереве информации о таблице. Причем сразу отобразится и код триггера или триггерной функции:
Примеры задач, для которых нужны триггеры
Триггеры BEFORE обычно используются для проверки корректности данных в таблице или аудита. AFTER подходят для заполнения или удаления данных в других таблицах, например при расчете итогов. Эти триггеры получают окончательные значения в переменной NEW, в то время как BEFORE может быть несколько и сложно контролировать их порядок.
В общем случае предпочтительно использовать триггеры BEFORE, так как они не требуют сохранения информации об операции до конца работы оператора. А с триггерами AFTER хороший тон — использовать условия WHEN, чтобы не хранить до конца работы оператора информацию по строкам, для которых не нужно выполнение триггерной функции.
Пример с триггером BEFORE и проверкой данных мы рассмотрели выше. Составим пример с аудитом действий, все с той же таблицей зарплат сотрудников:
-- Создаем таблицу зарплат сотрудников и таблицу для аудита
CREATE TABLE personal_salary (
emp_name text NOT NULL,
salary integer
);
CREATE TABLE personal_salary_audit (
operation char(1) NOT NULL,
date timestamp NOT NULL,
userid text NOT NULL,
emp_name text NOT NULL,
salary integer
);
-- Создаем триггерную функцию
CREATE OR REPLACE FUNCTION salary_audit() RETURNS TRIGGER AS $salary_audit$
BEGIN
-- Добавляем строку в personal_salary_audit, которая отражает операцию, выполняемую в personal_salary
-- Для определения типа операции применяется специальная переменная TG_OP
IF (TG_OP = 'DELETE') THEN
INSERT INTO personal_salary_audit SELECT 'D', now(), user, OLD.*;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO personal_salary_audit SELECT 'U', now(), user, NEW.*;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO personal_salary_audit SELECT 'I', now(), user, NEW.*;
END IF;
RETURN NULL; -- Возвращаемое значение для триггеров AFTER игнорируется
END;
$salary_audit$ LANGUAGE plpgsql;
-- Создаем триггер уровня строки:
CREATE TRIGGER salary_audit
AFTER INSERT OR UPDATE OR DELETE ON personal_salary
FOR EACH ROW EXECUTE PROCEDURE salary_audit();
-- Проверим, что все работает, как ожидаем, добавим строку в таблицу personal_salary
INSERT INTO personal_salary VALUES ('test', '500');
-- И посмотрим на результат:
SELECT * FROM personal_salary;
emp_name | test
salary | 500
SELECT * FROM personal_salary_audit;
operation | I
date | 2024-06-19 14:07:14.996449
userid | postgres
emp_name | test
salary | 500
Другие особенности триггеров и триггерных функций
Чтобы создавать триггеры, пользователь должен иметь привилегию TRIGGER для таблицы и EXECUTE для функции.
Если для одной и той же таблицы создано несколько триггеров, то отрабатывать они будут в алфавитном порядке по имени.
С операцией INSERT с предложением ON CONFLICT DO UPDATE возможно срабатывание триггеров BEFORE INSERT и BEFORE UPDATE.
Когда для таблицы задано несколько триггеров BEFORE или INSTEAD OF, измененная триггерной функцией строка одного триггера становится входящей строкой для следующего. Нужно учитывать это при работе с несколькими триггерами к одной таблице. Кроме того, если один из них возвращает NULL, то операция и все последующие триггеры для этой строки не отработают.
Триггерная функция может вызывать команды SQL — это способно привести к каскадному выполнению триггеров. Вполне возможно рекурсивное выполнение одного и того же триггера — программист может попасть в бесконечный цикл.
Заключение
Триггеры и триггерные функции предоставляют огромные возможности, но их нужно использовать с умом. Например, почти весь код приложения можно перенести в триггерные функции, однако обычно рекомендуют бизнес-логику оставить приложению. Размещение одной части логики работы в коде приложения, а другой в триггерной функции нежелательно — это серьезно усложняет дебаг.
Триггеры и триггерные функции хорошо использовать для аудита изменений, проверок корректности введенных данных и для прочих подобных задач. Это мощный механизм, но его нужно использовать разумно.
Дополнение. Доступные в триггерной функции переменные
NEW record — для триггеров уровня строки содержит новую запись, которая должна стать результатом операции INSERT/UPDATE.
OLD record — для триггеров уровня строки содержит старую запись, до выполнения UPDATE/DELET.
TG_NAME name — имя триггера.
TG_WHEN text — может содержать BEFORE, AFTER или INSTEAD OF — условие выполнения функции: до, после или вместо операции.
TG_LEVEL text — уровень триггера: для строки (ROW) или операции (STATEMENT).
TG_OP text — операция, для которой запускается триггерная функция: INSERT, UPDATE, DELETE или TRUNCATE.
TG_RELID oid (references pg_class.oid) — object ID таблицы, для которой сработал триггер.
TG_RELNAME name — таблица, для которой сработал триггер. Устарело, в будущих релизах PostgreSQL будет убрано, используйте TG_TABLE_NAME.
TG_TABLE_NAME name — таблица, для которой сработал триггер.
TG_TABLE_SCHEMA name — схема таблицы, для которой сработал триггер.
TG_NARGS integer — количество аргументов, данных триггерной функции при создании триггера.
TG_ARGV text[] — аргументы, переданные операции CREATE TRIGGER.