PostgreSQL-триггеры: создание, изменение и удаление — с примерами

PostgreSQL-триггеры — это хранимые процедуры, которые выполняются до, после или вместо определенной операции. Например, при вызове UPDATE выполняется функция, проверяющая корректность новых данных. В статье разбираемся, как использовать этот механизм при проектировании БД. Все примеры подходят для PostgreSQL 17 версии и выше.

Синтаксис триггера

Помимо определения триггера, важно знать, как написать функцию. Ниже разобрали синтаксис обоих конструкций.

Синтаксис функции

Так как триггер вызывает функцию, ее нужно создать перед определением триггера. Функция может принимать аргументы, которые доступны через массив TG_ARGV, и должна возвращать NEW, OLD или NULL. В общем виде это выглядит так:

CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER AS
$$
BEGIN
	RETURN NEW;
END;
$$
LANGUAGE plpgsql;
/*Далее идет определение триггера*/

LANGUAGE plpgsql в примере указывает PostgreSQL, что для описания функции используется язык PL/pgSQL. Это расширение для SQL, доступное только в PostgreSQL, которое поддерживает переменные, циклы, условия и обработку ошибок.

Базовый синтаксис

Базовый синтаксис триггера можно описать так:

CREATE TRIGGER trigger_name
	BEFORE UPDATE
	ON table_name
	FOR EACH ROW
	EXECUTE FUNCTION function_name();

Разберем конструкцию по строкам:

• CREATE TRIGGER trigger_name — создает новый триггер.
• BEFORE UPDATE — указывает, что функция будет вызываться только перед применением операции UPDATE. Вместо BEFORE можем указать также AFTER или INSTEAD OF. О разнице между ними поговорим ниже.
• ON table_name — имя таблицы.
• FOR EACH ROW — тип триггера.
• EXECUTE FUNCTION function_name(); — указываем имя функции.

Триггеры можно задать для операторов: INSERT, UPDATE, DELETE или TRUNCATE.

Условия

Для UPDATE дополнительно можно указать, чтобы триггер запускался, только если в SET указаны названия определенных столбцов. Делается это так:

BEFORE UPDATE OF quantity, product_name
ON table_name

Также в триггере можно использовать оператор WHEN для дополнительного условия. Например:

CREATE TRIGGER trigger_name
	BEFORE UPDATE
	ON table_name
	FOR EACH ROW
	WHEN (NEW.quantity < 0)
	EXECUTE FUNCTION function_name();

Триггер срабатывает на любой UPDATE, но функция сработает только при выполнении условия WHEN (NEW.quantity < 0).

NEW — ключевое слово для доступа к данным, которые пользователь пытается добавить в таблицу. Аналогично в триггерах доступно слово OLD — для доступа к старым данным.

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

Существует два типа триггеров: DML — для операций с данными — и DDL — для изменения структуры. В таблицах PostgreSQL реализованы только DML-триггеры, поэтому о них и будем говорить.

По уровню

Существует два основных типа:

• FOR EACH ROW — на уровне строки. Означает, что функция в триггере выполняется для каждой строки. Например, если UPDATE обновляет 100 строк, то для каждой из них сработает код из функции.
• FOR EACH STATEMENT — на уровне оператора. Означает, что функция выполняется один раз независимо от количества затронутых строк.

Для оператора TRUNCATE можно использовать только триггеры типа STATEMENT, а для всех остальных — оба типа.

По порядку выполнения

На выполнение триггера влияют ключевые слова BEFORE, AFTER и INSTEAD OF — порядок зависит от уровня ROW или STATEMENT.

• BEFORE на уровне STATEMENT: вызывают функцию до того, как применяется оператор. Ничего не знает об OLD и NEW в конкретной строке, поэтому используется обычно для логирования или запрета всей операции.
• BEFORE на уровне ROW: вызывается перед обработкой каждой строки. Может получить доступ к OLD и NEW, поэтому часто используется для проверки данных.
• AFTER на уровне оператора: вызывается в самом конце. Обычно применяется для логирования.
• AFTER на уровне строки: выполняется после применения изменений к строке, но до AFTER уровня оператора.
• INSTEAD OF: этот тип можно определить только на уровне строки в таблице представления. Действия, заданные в функции триггера, выполняются вместо текущего оператора.

Constraint Trigger

В PostgreSQL существуют CONSTRAINT, Это правила, которые устанавливают, какие данные можно вставлять и удалять в столбце. Они позволяют превратить СУБД в «умное» хранилище, не принимающее в себя неправильные значения.

В случае с триггерами это используется, чтобы отложить выполнение кода функции до конца транзакции с помощью параметров DEFERRABLE / INITIALLY DEFERRED. Простой CREATE TRIGGER этого сделать не может, поэтому используют конструкцию CREATE CONSTRAINT TRIGGER.

Рассмотрим пример. В базе есть две таблицы:

department:

employee:

Наша задача проверить условие: если пользователь хочет повысить зарплату сотруднику, а сумма столбца salary превышает запись budget из таблицы department — выдать ошибку; если нет — применить изменения.

С помощью стандартных ограничений решить проблему не получится, поэтому будем использовать триггер:

--функция
CREATE OR REPLACE FUNCTION check_department_budget()
RETURNS trigger
AS $$
DECLARE str text;
BEGIN
	-- если сумма всех зарплат больше общего бюджета
	IF
	(SELECT sum(salary) FROM employee WHERE department_id = NEW.dep_id)
	>
	(SELECT budget FROM department WHERE department_id = NEW.department_id)
THEN
	RAISE EXCEPTION 'Overbudget', NEW.dep_id; --если условие выше срабатывает, выводим ошибку
END IF;
END;
$$ LANGUAGE plpgsql

--триггер
CREATE CONSTRAINT TRIGGER check_department_budget_trigger
	AFTER INSERT OR UPDATE
	ON employee
	DEFFERABLE INITIALLY DEFFERED
	FOR EACH ROW
	EXECUTE FUNCTION check_department_budget();

Если не использовать CONSTRAINT TRIGGER в этом примере, то функция будет вызываться для каждой строки. Например, пользователь захочет рассчитать зарплату сотрудника с учетом всех премий и штрафов:

UPDATE employee SET salary = salary + 500;

UPDATE employee SET salary = salary - 500;  

Без ограничителя триггер сработает уже на первой строчке и вызовет ошибку, прервав транзакцию, а с ограничителем — в самом конце, после всех изменений.

Операции с триггерами в pgAdmin

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

Код:

CREATE TABLE music_table(
	id serial PRIMARY KEY,
	title text NOT NULL,
	artist text NOT NULL,
	release_date date,
	genre text
);
INSERT INTO music_table (title, artist, release_date, genre)
VALUES
    ('Shape of You', 'Ed Sheeran', '2017-01-06', 'Pop'),
    ('Blinding Lights', 'The Weeknd', '2019-11-29', 'Synthpop'),
    ('Smells Like Teen Spirit', 'Nirvana', '1991-09-10', 'Rock'),
    ('Bad Guy', 'Billie Eilish', '2019-03-29', 'Pop'),
    ('Billie Jean', 'Michael Jackson', '1982-01-02', 'Pop');

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

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

Все созданные типы TRIGGER можно посмотреть на левой панели в соответствующем разделе.

Код:

CREATE OR REPLACE FUNCTION set_default_genre()
RETURNS trigger AS $$
BEGIN
    IF NEW.genre IS NULL OR NEW.genre = '' THEN
        NEW.genre := 'Unknown';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_set_default_genre
BEFORE INSERT ON music_table
FOR EACH ROW
EXECUTE FUNCTION set_default_genre();

Изменение и удаление

Переименовать TRIGGER можно через ALTER:

Если хотите поменять функцию или оператор, придется удалить триггер и создать его заново. Для этого воспользуйтесь DROP, а затем — CREATE. Например, поменяем поведение функции. Вместо установки значения default, пусть БД выводит ошибку и прерывает выполнение.

Чтобы изменения отобразились в левой панели, кликните правой кнопкой мыши по пункту Table и выберите Refresh.

Код:

--удаляем старый триггер
DROP TRIGGER new_trigger_name ON music_table;

--создаем новый
CREATE OR REPLACE FUNCTION set_default_genre()
RETURNS trigger AS $$
BEGIN
    IF NEW.genre IS NULL OR NEW.genre = '' THEN
	-- выведем ошибку в консоль вместо установки стандартного значения
RAISE EXCEPTION 'Genre must be provided for track "%" by "%"', NEW.title, NEW.artist;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_default_genre
BEFORE INSERT ON music_table
FOR EACH ROW
EXECUTE FUNCTION set_default_genre();

Отображение списка триггеров

Все триггеры хранятся в information_schema.triggers. Чтобы достать их оттуда, можно воспользоваться обычным SELECT:

В information_schema.triggers довольно много столбцов и далеко не все из них нужны. Поэтому в запросе, вместо звездочки, перечислили названия основных:

• trigger_catalog — название БД;
• trigger_name — имя триггера;
• event_manipulation — тип операции;
• event_object_table — таблица, на которую влияет триггер.

Значение остальных столбцов можно посмотреть в документации.

Код:

SELECT
trigger_catalog, trigger_name, event_manipulation, event_object_table
FROM information_schema.triggers;

Отключение и включение

Для отключения и включения триггера используют DISABLE/ENABLE TRIGGER в составе ALTER TABLE:

Особенности SQL-триггеров

Полный список особенностей — в документации; ниже — только основные моменты.

Каскадные PostgreSQL триггеры. Если в функции SQL-триггера есть операторы, то они также могут вызывать другие триггеры. Например, на команду INSERT повесили TRIGGER, функция которого вставляет данные в таблицу с помощью того же оператора INSERT, вызывая тем самым повторное срабатывание процедуры. В таком случае произойдет рекурсия и триггеры будут бесконечно вызывать друг друга.

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

Для MERGE нет отдельного триггера. Если нужно создать хранимую процедуру для MERGE, можно воспользоваться операторами UPDATE, DELETE и INSERT с условием: WHEN MATCHED THEN UPDATE…

Заключение

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