Введение

Из этой статьи вы узнаете, что такое триггеры PostgreSQL, чем они отличаются от стандартных триггеров SQL, а также как они устроены. Мы покажем на простом примере, как создать, удалить триггер PostgreSQL и убедиться, что всё работает нормально.

Что такое триггеры

Триггеры PostgreSQL – функции, запускаемые автоматически при выполнении определенного вида запроса к таблице. Всего таких запросов четыре:

INSERT
UPDATE
DELETE
TRUNCATE

Сам триггер состоит из двух частей – функции и правила ее запуска.

Типы триггеров

В PostgreSQL есть 2 основных типа триггера:

row-level
statement-level

Главное, чем различаются эти типы, – то, сколько раз функция будет выполняться при осуществлении заданного запроса. Для row-level триггера при запросе, затрагивающем 50 строк, триггерная функция будет выполнена 50 раз – по 1 разу для каждой затронутой запросом строки, а для statement-level – только 1 раз для каждого события, вызывающего срабатывание триггера.

Кроме того, триггеры в PostgreSQL различаются по периоду выполнения относительно запроса – триггерная функция может запускаться как до применения самого запроса, так и после. В первом случае функция будет работать с неизмененными данными, во втором – с данными, модифицированными запросом. Учитывайте эти условия, когда проектируете триггеры.

Отличия от стандартных триггеров SQL

Хотя PostgreSQL основан на стандарте SQL, триггеры в этой СУБД пригодятся для решения разных задач, в частности:

PostgreSQL позволяет создавать триггеры для TRUNCATE-запросов.
СУБД дает возможность создавать statement-level триггеры для представлений.
В PostgreSQL необходимо создавать триггерную функцию в качестве действия, выполняемого триггером, в то время как стандарт SQL работает с любыми SQL-запросами.

Использование триггеров

Триггеры в БД нужны, чтобы:

Распределять нагрузку по обработке данных между сервером приложения и сервером БД.
Снижать количество необходимых запросов со стороны приложения к БД за счет предварительной обработки данных на стороне сервера БД.
Кэшировать данные с помощью материализованных представлений, которые вместе с триггерами обрабатывают и сохраняют данные в формате таблиц, позволяя избежать повторных расчетов.
Реализовывать общий функционал приложений, использующих одну базу данных, тем самым избегая дублирования кода.

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

Создание триггера

Что нужно сделать, чтобы запустить триггер:

Сначала создайте триггерную функцию, используя запрос вида CREATE FUNCTION
Свяжите созданную функцию с таблицей, используя запрос CREATE TRIGGER

Создание триггерной функции

Сама триггерная функция похожа на обычную пользовательскую функцию, однако не принимает аргументов и возвращает данные типа trigger.

Синтаксис функции выглядит так:

CREATE FUNCTION trigger_function() 
   RETURNS TRIGGER 
   LANGUAGE PLPGSQL
AS $$
BEGIN
   -- trigger logic
END;
$$

Создать триггерную функцию можно, используя любой из языков, поддерживаемых PostgreSQL. Узнать установленные языки можно, выполнив запрос SELECT * FROM pg_language; или команду \dL в командной строке psql:

default_db=> \dL
                     	Список языков
   Имя   | Владелец | Доверенный |       	Описание      	 
---------+----------+------------+------------------------------
 plpgsql | postgres | t      	| PL/pgSQL procedural language
(1 строка)

В рамках этой статьи будет использоваться процедурный язык PL/pgSQL. Данные об окружении триггерная функция получает из структуры TriggerData, в которой находятся переменные окружения. Они содержат состояние ряда или таблицы до и после выполнения функции, а также ряд локальных переменных с префиксом TG_ – например, TG_TABLE_NAME, содержащую название таблицы.

Привязка триггерной функции к таблице

Запрос вида CREATE TRIGGER позволяет создать триггер, запускающий заданную функцию при том или ином запросе к таблице.

Синтаксис запроса выглядит так:

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
   [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

Три момента, которые стоит учесть при создании триггера:

Добавление OR REPLACE в запрос создания триггера заменит уже существующий триггер.
Триггеры с опцией INSTEAD OF должны быть помечены опцией FOR EACH ROW и могут быть определены только в представлениях. Триггеры, которые выполняются до (BEFORE) или после события (AFTER), в представлении должны быть помечены как FOR EACH STATEMENT. Эта таблица в документации PostgreSQL поможет визуализировать данные ограничения.
Триггеры не могут вызываться при выполнении SELECT-запросов, поскольку SELECT-запрос не может изменять данные. В качестве альтернативы можно использовать представления и правила.

Пример использования триггера

Мы рассмотрим следующий пример использования триггеров PostgreSQL: у нас есть таблица с именами сотрудников, содержащая их ID, имя и фамилию. При этом нам необходимо отслеживать историю изменений имен и фамилий сотрудников. SQL-запрос, позволяющий создать таблицу с сотрудниками, выглядит так:

CREATE TABLE employees(
   id INT GENERATED ALWAYS AS IDENTITY,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   PRIMARY KEY(id)
);

Также создадим таблицу для логирования изменений:

CREATE TABLE employee_name_history (
   id INT GENERATED ALWAYS AS IDENTITY,
   employee_id INT NOT NULL,
   first_name VARCHAR(40) NOT NULL,
   last_name VARCHAR(40) NOT NULL,
   changed_on TIMESTAMP NOT NULL
);

Теперь мы готовы к созданию триггера. Создадим триггерную функцию следующего вида:

CREATE OR REPLACE FUNCTION log_last_name_changes()
  RETURNS TRIGGER
  LANGUAGE PLPGSQL
  AS
$$
BEGIN
    IF NEW.last_name <> OLD.last_name THEN
   	  INSERT INTO employee_name_history(employee_id,first_name,last_name,changed_on)
   	  VALUES(OLD.id,OLD.first_name,OLD.last_name,now());
    END IF;

    RETURN NEW;
END;
$$;

Данная функция сравнивает значения фамилии сотрудника до и после выполнения запроса, а затем вносит в таблицу с историей старые значения имени и фамилии, а также id сотрудника и дату изменения.

Далее создадим сам триггер, привязывая созданную функцию к определенному запросу к выбранной таблице:

CREATE TRIGGER last_name_changes
  BEFORE UPDATE
  ON employees
  FOR EACH ROW
  EXECUTE PROCEDURE log_last_name_changes();

Данный триггер будет выполняться перед каждым запросом типа UPDATE к таблице employees, выполняя функцию log_last_name_changes(), созданную нами ранее, для каждого затронутого ряда.

Заполним таблицу тестовыми данными и проверим работу триггера:

INSERT INTO employees (first_name, last_name)
VALUES ('Rodion', 'Raskolnikov');
INSERT INTO employees (first_name, last_name)
VALUES ('Natalie', 'Rostova');

Проверим, что данные записались корректно:

SELECT * FROM employees;
  1 | Rodion 	| Raskolnikov
  2 | Natalie	| Rostova

Теперь проверим работу триггера. Для этого нам потребуется выполнить UPDATE-запрос к таблице employees. Попробуем изменить запись с ID = 2:

UPDATE employees 
SET last_name = 'Bezuhova' 
WHERE ID = 2;

Проверим, что данные в таблице изменились:

SELECT * FROM employees;
  1 | Rodion 	| Raskolnikov
  2 | Natalie	| Bezuhova

Отлично, фамилия поменялась. Теперь проверим таблицу last_name_changes – в ней должна появиться запись со старой фамилией:

SELECT * FROM employee_name_history;
  1 |       	2 | Natalie	| Rostova   | 2024-07-03 18:26:17.376749

Триггер успешно отработал – в таблицу изменений записались старые имя, фамилия, а также дата изменения записи о сотруднике.

Удаление триггера

Для того чтобы удалить существующий триггер, используется запрос DROP TRIGGER, который имеет следующий синтаксис:

DROP TRIGGER [IF EXISTS] trigger_name
ON table_name
[ CASCADE | RESTRICT ];

В данном запросе:

Вместо trigger_name указывается название триггера.
Ключ IF EXISTS удаляет триггер, только если он существует. Если выполнить запрос при отсутствующем ключе для несуществующего триггера, PostgreSQL выдаст ошибку, в противном случае – только уведомление.
Далее указывается таблица, для которой удаляется триггер.
Опция CASCADE позволяет удалить объекты, зависящие от данного триггера.
Опция RESTRICT, наоборот, запрещает удаление триггера, если есть зависящие от него объекты.

Обратите внимание!

Данный запрос удаляет только сам триггер, но не функцию, используемую в нем. Для удаления функции используйте запрос DROP FUNCTION:

drop function [if exists] function_name(argument_list)
[cascade | restrict]

Значения ключей в нем аналогичны DROP TRIGGER.

Заключение

В данной статье мы рассказали, как создать триггер в PostgreSQL и удалить его, а также поделились возможными вариантами его использования. Для получения наиболее полной информации о триггерах и процедурах рекомендуем ознакомиться с документацией PostgreSQL.

В рамках статьи мы использовали пример триггера, реагирующего на изменение поля фамилии. Если вы следовали данному примеру вместе с нами, вы могли заметить, что на данный момент триггер не выполняется, если меняется имя сотрудника. Попробуйте пересоздать указанную функцию таким образом, чтобы старая версия имени записывалась при изменении как имени, так и фамилии. Подсказка – pgsql поддерживает логические операторы. Обсудить результат вы можете в наших сообществах 🙂

Если возникнут вопросы, напишите нам, пожалуйста, тикет из панели управления аккаунта (раздел «Помощь и поддержка»). А если вы захотите обсудить эту статью, триггеры БД PostgreSQL, примеры их использования, наши продукты или свой проект с коллегами по цеху и сотрудниками Бегета – ждем вас в нашем сообществе в Telegram.

PostgreSQL-триггеры: примеры создания, использования и удаления функций