PL/pgSQL
Триггеры
16
Авторские права
© Postgres Professional, 2017–2024
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Триггеры и триггерные функции
В какой момент срабатывают триггеры
Контекст выполнения триггерной функции
Возвращаемое значение
Для чего можно применять триггеры и для чего не нужно
Событийные триггеры
3
Триггеры и функции
Триггер
объект базы данных — список обрабатываемых событий
при возникновении события вызывается триггерная функция
и ей передается контекст
Триггерная функция
объект базы данных — код обработки события
выполняется в той же транзакции, что и основная операция
соглашение: функция не принимает параметры,
возвращает значение псевдотипа trigger (фактически record)
может использоваться в нескольких триггерах
Механизм триггеров позволяет выполнять определенные действия
«в ответ» на определенные события. Триггер состоит из двух частей:
собственно триггера (который определяет события) и триггерной
функции (которая определяет действия). И триггер, и функция
являются самостоятельными объектами БД.
Когда возникает событие, на которое «подписан» триггер, вызывается
триггерная функция. Ей передается контекст вызова, чтобы можно
было определить, какой именно триггер и в каких условиях вызвал
функцию.
Триггерная функция — это обычная функция, которая написана
с учетом некоторых соглашений:
●
она пишется на любом языке, кроме чистого SQL;
●
она не имеет параметров;
●
она возвращает значение типа trigger (на самом деле это псевдотип,
по факту возвращается запись, соответствующая строке таблицы).
Триггерная функция выполняется в той же транзакции, что и основная
операция. Таким образом, если триггерная функция завершится
с ошибкой, вся транзакция будет прервана.
4
События
INSERT, UPDATE, DELETE
таблицы before/after statement
before/after row
представления before/after statement
instead of row
TRUNCATE
таблицы before/after statement
Условие WHEN
устанавливает дополнительный фильтр
Триггеры могут срабатывать на вставку (INSERT), обновление
(UPDATE) или удаление (DELETE) строк в таблице или представлении,
а также на опустошение (TRUNCATE) таблиц.
Триггер может срабатывать до выполнения действия (BEFORE),
после него (AFTER), или вместо него (INSTEAD OF).
Триггер может срабатывать один раз для всей операции (FOR EACH
STATEMENT), или каждый раз для каждой затронутой строки (FOR
EACH ROW).
Не для любой комбинации этих условий можно создать триггер.
Например, instead-of-триггеры можно определить только для
представлений на уровне строк, а truncate-триггер можно определить
только для таблиц и только на уровне оператора. Допустимые
варианты перечислены на слайде.
Кроме того, можно сузить область действия триггера, указав
дополнительное условие WHEN: если условие не выполняется —
триггер не срабатывает.
5
Before statement
Срабатывает
до операции
Возвращаемое значение
игнорируется
Контекст
TG-переменные
BEFORE STATEMENT
выполнение операции
Рассмотрим подробнее разные типы триггеров.
Триггер BEFORE STATEMENT срабатывает один раз для операции
независимо от того, сколько строк будет затронуто (возможно, что
и ни одной). Это происходит до того, как операция начала выполняться.
Возвращаемое значение триггерной функции игнорируется. Если
в триггере возникает ошибка, операция отменяется.
Поскольку триггерная функция не имеет параметров, контекст вызова
в PL/pgSQL передается ей с помощью предопределенных TG-
переменных, таких, как:
●
TG_WHEN = «BEFORE»,
●
TG_LEVEL = «STATEMENT»,
●
TG_OP = «INSERT»/«UPDATE»/«DELETE»/«TRUNCATE»
и др. Триггерной функции можно также передать пользовательский
контекст (аналог отсутствующих параметров) через переменную
TG_ARGV, хотя зачастую лучше вместо одной «обобщенной» функции
создать несколько «частных».
6
Before row
Срабатывает
перед действием со строкой
в процессе выполнения операции
Возвращаемое значение
строка (возможно, измененная)
null отменяет действие
Контекст
OLD update, delete
NEW insert, update
TG-переменные
BEFORE STATEMENT
выполнение операции
BEFORE ROW
Триггеры BEFORE ROW срабатывают каждый раз перед тем, как
операция затронет строку. Это происходит непосредственно в процессе
выполнения операции.
В качестве контекста триггерная функция получает переменные:
●
OLD — старая строка (не определено для операции вставки),
●
NEW — измененная строка (не определено для удаления),
●
TG_WHEN = «BEFORE»,
●
TG_LEVEL = «ROW»,
●
TG_OP = «INSERT»/«UPDATE»/«DELETE»
и др.
Возврат неопределенного значения NULL воспринимается как отмена
действия над данной строкой. Сама операция продолжит выполнение,
но текущая строка не будет обработана и другие триггеры для этой
строки не сработают.
Чтобы не вмешиваться в работу операции, триггер должен вернуть
строку в том виде, в котором ее собирается изменить операция: NEW
для вставки и обновления, любое значение (но не NULL) для удаления
(обычно используют OLD).
Но триггерная функция может и изменить значение NEW, чтобы
повлиять на результат операции — часто именно для этого такой
триггер и создают.
7
Instead of row
Срабатывает
вместо действия со строкой
для представлений
Возвращаемое значение
строка (возможно, измененная) —
будет видна в RETURNING
null отменяет действие
Контекст
OLD update, delete
NEW insert, update
TG-переменные
BEFORE STATEMENT
выполнение операции
BEFORE ROW
INSTEAD OF ROW
Триггеры INSTEAD OF очень похожи на триггеры BEFORE,
но определяются только для представлений и срабатывают не до,
а вместо операции.
В задачу таких триггеров обычно входит выполнение необходимых
операций над базовыми таблицами представления. Также триггер
может вернуть измененное значение NEW — именно оно будет видно
при выполнении операции с указанием фразы RETURNING.
8
After row
Срабатывает
после выполнения операции
очередь из прошедших условие WHEN
Возвращаемое значение
игнорируется
Контекст
OLD, OLD TABLE update, delete
NEW, NEW TABLE insert, update
TG-переменные
BEFORE STATEMENT
AFTER ROW
выполнение операции
BEFORE ROW
INSTEAD OF ROW
Триггеры AFTER ROW, как и BEFORE ROW, срабатывают для каждой
затрагиваемой строки, но не сразу после действия над строкой,
а после того, как выполнена вся операция - чтобы при обращении
из этих триггеров к изменяющейся таблице результат не зависел
от порядка обработки строк. Для этого события сначала помещаются
в очередь и обрабатываются после окончания операции. Чем меньше
событий попадет в очередь, тем меньше будет накладных расходов —
поэтому именно в этом случае очень полезно использовать условие
WHEN, чтобы отсечь заведомо ненужные строки.
Возвращаемое значение триггеров AFTER ROW игнорируется
(поскольку операция уже выполнена).
Контекст триггерной функции составляют:
●
OLD — старая строка (не определено для операции вставки),
●
NEW — новое значение строки (не определено для удаления).
Кроме этих переменных, начиная с версии 10 триггерная функция
может получить доступ к специальным переходным таблицам
(transition tables). Таблица, указанная при создании триггера как OLD
TABLE, содержит старые значения строк, обработанных триггером,
а таблица NEW TABLE — новые значения тех же строк.
Доступны и обычные TG-переменные, включая:
●
TG_WHEN = «AFTER»,
●
TG_LEVEL = «ROW»,
●
TG_OP = «INSERT»/«UPDATE»/«DELETE».
9
After statement
Срабатывает
после операции
(даже если не затронута ни одна строка)
Возвращаемое значение
игнорируется
Контекст
OLD TABLE update, delete
NEW TABLE insert, update
TG-переменные
BEFORE STATEMENT
AFTER STATEMENT
AFTER ROW
выполнение операции
BEFORE ROW
INSTEAD OF ROW
Триггер AFTER STATEMENT срабатывает один раз после окончания
операции и после всех триггеров AFTER ROW (независимо от того,
сколько строк было затронуто).
Возвращаемое значение триггерной функции игнорируется.
Контекст вызова передается с помощью переходных таблиц.
Обращаясь к ним, триггерная функция может проанализировать все
затронутые строки. Обычно переходные таблицы используются именно
с триггерами AFTER STATEMENT, а не AFTER ROW.
Также определены обычные TG-переменные:
●
TG_WHEN = «AFTER»,
●
TG_LEVEL = «STATEMENT»,
●
TG_OP = «INSERT»/«UPDATE»/«DELETE»/«TRUNCATE»
и др.
11
Возможное использование
изменение базовых таблиц представления
проверка согласованности,
в том числе на уровне таблицы;
«аудит» операций;
каскадное изменение таблиц
(денормализация, асинхронная
обработка...)
проверка применимости операции
проверка корректности;
модификация строки
BEFORE STATEMENT
AFTER STATEMENT
AFTER ROW
выполнение операции
BEFORE ROW
INSTEAD OF ROW
Каково практическое применение триггеров?
BEFORE-триггеры можно использовать для проверки корректности
операции и при необходимости вызывать ошибку.
Триггеры BEFORE ROW можно применять для модификации строки
(например, заполнить пустое поле нужным значением). Это бывает
удобно, чтобы не повторять логику заполнения «технических» полей
в каждой операции, а также позволяет вмешаться в работу
приложения, код которого недоступен для изменения.
Триггеры INSTEAD OF ROW применяют для того, чтобы отобразить
операции над представлением в операции над базовыми таблицами.
Триггеры AFTER ROW и AFTER STATEMENT полезны в случаях, когда
нужно знать точное состояние после операции (BEFORE-триггеры
могут влиять на результат, так что на этом этапе ясности еще нет):
●
для проверки согласованности операции;
●
для «аудита», то есть записи изменений в отдельное хранилище;
●
для каскадного изменения других таблиц (например, обновлять
денормализованные данные при изменении базовых таблиц, или
записывать изменения в очередь для последующей обработки вне
данной транзакции).
Если операции затрагивают сразу много строк, то триггер AFTER
STATEMENT с переходными таблицами может оказаться более
эффективным решением, чем триггер AFTER ROW, поскольку
позволяет обрабатывать все изменения пакетно, а не построчно.
12
Сложности
Код вызывается неявно
сложно отследить логику выполнения
Правила видимости изменчивой триггерной функции
виден результат триггеров BEFORE ROW или INSTEAD OF ROW
Порядок вызова триггеров для одного события
триггеры отрабатывают в алфавитном порядке
Не предотвращается зацикливание
триггер может вызвать срабатывание других триггеров
Можно нарушить ограничения целостности
например, исключив из обработки строки, которые должны удалиться
Однако не стоит злоупотреблять триггерами. Триггеры срабатывают
неявно, что сильно затрудняет понимание логики работы и крайне
усложняет поддержку приложения. Попытки реализовать сложную
логику на триггерах обычно заканчиваются плачевно.
В некоторых случаях вместо триггеров можно использовать
вычисляемые поля (GENERATED ALWAYS AS … STORED). Такое
решение — если оно подходит — будет заведомо проще и прозрачнее.
Есть ряд тонкостей, связанных с триггерами, которые мы сознательно
не рассматриваем подробно:
●
правила видимости изменчивых (volatile) функций в триггерах
BEFORE ROW и INSTEAD OF ROW (не стоит обращаться к таблице,
полагаясь на порядок, в котором сработают триггеры);
●
порядок вызова нескольких триггеров, обрабатывающих одно и то же
событие (не стоит усугублять и без того неявное срабатывание
триггеров завязкой на последовательность обработки);
●
возможность зацикливания в случае каскадного срабатывания
других триггеров, которые, в свою очередь, могут приводить к новым
срабатываниям данного триггера;
●
возможность нарушить ограничения целостности (например, при
исключении из обработки строки, которая удаляется условием ON
DELETE CASCADE, может быть нарушена ссылочная целостность).
Если вы столкнулись с тем, что эти тонкости важны для вашего
приложения — серьезно задумайтесь.
14
Событийные триггеры
Событийный триггер
похож на обычный «табличный» триггер, но другой объект
Триггерная функция
соглашение: функция не принимает параметры,
возвращает значение псевдотипа event_trigger
для получения контекста служат специальные функции
События
DDL_COMMAND_START перед выполнением команды
DDL_COMMAND_END после выполнения команды
TABLE_REWRITE перед перезаписью таблицы
SQL_DROP после удаления объектов
Событийные триггеры — по сути те же триггеры, но срабатывают они
не на DML-, а на DDL-операции (CREATE, ALTER, DROP, COMMENT,
GRANT, REVOKE) и не привязаны к определенному объекту базы
данных.
Такие триггеры являются не инструментом разработки приложений,
а скорее служат для решения задач администрирования. Поэтому
здесь мы упоминаем их только для полноты картины и рассмотрим
только простые примеры.
16
Итоги
Триггер — способ отреагировать на возникновение события
С помощью триггера можно отменить операцию,
изменить ее результат или выполнить дополнительные
действия
Триггер выполняется как часть транзакции;
ошибка в триггере приводит к откату транзакции
Использование триггеров AFTER ROW и переходных таблиц
удорожает обработку
Все хорошо в меру: сложную логику трудно отлаживать
из-за неявного выполнения триггеров
17
Практика
1. Создайте триггер, обрабатывающий обновление поля
onhand_qty представления catalog_v.
Проверьте, что в «Каталоге» появилась возможность
заказывать книги.
2. Обеспечьте выполнение требования согласованности:
количество книг на складе не может быть отрицательным
(нельзя купить книгу, которой нет в наличии).
Внимательно проверьте правильность реализации, учитывая,
что с приложением могут одновременно работать несколько
пользователей.
2. Может показаться, что достаточно создать AFTER-триггер на таблице
operations, подсчитывающий сумму qty_change. Однако на уровне
изоляции Read Committed, с которым работает приложение «Книжный
магазин», нам придется блокировать таблицу operations в эксклюзивном
режиме — иначе возможны сценарии, при которых такая проверка
не сработает.
Лучше поступить следующим образом: добавить в таблицу books поле
onhand_qty и создать триггер, изменяющий это поле при изменении
таблицы operations (то есть, фактически, выполнить денормализацию
данных). На поле onhand_qty теперь можно наложить ограничение
CHECK, реализующее требование согласованности. А функция
onhand_qty(), которую мы создавали ранее, больше не нужна.
Особое внимание надо уделить начальной установке значения,
учитывая, что одновременно с выполнением наших операций в системе
могут работают пользователи.
18
Практика+
1. Напишите триггер, увеличивающий счетчик (поле version)
на единицу при каждом изменении строки. При вставке
новой строки счетчик должен устанавливаться в единицу.
Проверьте правильность работы.
2. Даны таблицы заказов (orders) и строк заказов (lines).
Требуется выполнить денормализацию: автоматически
обновлять сумму заказа в таблице orders при изменении
строк в заказе.
Создайте необходимые триггеры с использованием
переходных таблиц для минимизации операций обновления.
2. Для создания таблиц используйте команды:
CREATE TABLE orders (
id int PRIMARY KEY,
total_amount numeric(20,2) NOT NULL DEFAULT 0
);
CREATE TABLE lines (
id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
order_id int NOT NULL REFERENCES orders(id),
amount numeric(20,2) NOT NULL
);
Столбец orders.total_amount должен автоматически вычисляться
как сумма значений столбца lines.amount всех строк, относящихся
к соответствующему заказу.
