Триггеры

Евгения Кузина

Обзор

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

Для таблиц и внешних таблиц можно определить триггеры, которые будут выполняться до или после любой операции INSERT, UPDATE или DELETE, либо один раз для каждой измененной строки, либо один раз для каждого оператора SQL. Триггеры UPDATE также могут быть сконфигурированы таким образом, чтобы срабатывать только в том случае, если определенные столбцы упомянуты в выражении SET оператора UPDATE. Триггеры также могут срабатывать для операторов TRUNCATE.

Если происходит событие триггера, для обработки этого события вызывается функция триггера. Триггерные функции могут быть написаны на большинстве доступных процедурных языков, включая PL/pgSQL, PL/Tcl, PL/Perl и PL/Python.

В представлениях можно определить триггеры для выполнения вместо операций INSERT, UPDATE или DELETE. Триггеры INSTEAD OF срабатывают один раз для каждой строки представления, которую необходимо изменить. Триггерная функция отвечает за внесение изменений в базовые таблицы представления и, при необходимости, за возврат измененных строк в том виде, в каком они будут отображаться в представлении. Триггеры представлений также можно определить для выполнения до или после операций INSERT, UPDATE или DELETE. Однако эти триггеры срабатывают только в том случае, если в представлении также есть триггер INSTEAD OF. В противном случае все операторы, обращающиеся к представлению, должны быть переписаны в виде операторов, обращающихся к базовым таблицам, тогда будут срабатывать триггеры, установленные для этих таблиц.

Триггерная функция создается до определения триггера. Она должна быть объявлена как функция без аргументов и возвращать тип TRIGGER. Одну и ту же функцию можно использовать для нескольких триггеров.

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

Триггеры уровня строки и уровня оператора

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

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

Эти два типа триггеров называются триггерами уровня строки и триггерами уровня оператора соответственно.

Триггеры для оператора TRUNCATE можно определить только на уровне оператора, а не на уровне строки.

Триггеры BEFORE, AFTER и INSTEAD OF

Триггеры также классифицируются в зависимости от того, срабатывают ли они до, после или вместо операции. Триггеры BEFORE на уровне оператора срабатывают до того, как оператор начнет что-либо делать, триггеры AFTER на уровне оператора срабатывают в конце оператора. Эти типы триггеров могут быть определены для таблиц, представлений или внешних таблиц.

Триггеры BEFORE на уровне строки срабатывают перед обработкой конкретной строки, триггеры AFTER на уровне строки срабатывают в конце оператора, но до срабатывания любого триггера AFTER на уровне оператора. Эти типы триггеров не рекомендуется использовать с представлениями.

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

Обычно триггеры BEFORE на уровне строки используются для проверки или изменения данных, которые будут вставлены или обновлены. Например, триггер BEFORE можно использовать для вставки текущего времени в столбец типа timestamp или для проверки согласованности двух элементов строки. Триггеры AFTER на уровне строк эффективны для каскадного обновления других таблиц или проверки соответствия внесенных изменений данным в других таблицах.

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

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

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

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

  1. Создать триггерную функцию.

  2. Использовать оператор CREATE TRIGGER, чтобы привязать триггерную функцию к отношению.

Рассмотрим пример, в котором логируются обновления — изменения существующей таблицы сохраняются в отдельной таблице. В примере используется триггер BEFORE UPDATE.

Существует таблица books, в которой хранится информация о книгах:

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    book_name VARCHAR NOT NULL,
    price NUMERIC NOT NULL
);

INSERT INTO
    books( book_name, price)
VALUES
    ('Hyperion',21),
    ('War and Peace',26),
    ('1984',20),
    ('The Time Machine',19);

Создайте новую таблицу для сохранения изменений:

CREATE TABLE books_log (
    id SERIAL PRIMARY KEY,
    book_id INT,
    book_name VARCHAR NOT NULL,
    price NUMERIC NOT NULL,
    change_date TIMESTAMP NOT NULL
);

Создайте триггерную функцию, используя для этого процедурный язык. Код ниже иллюстрирует синтаксис триггерной функции на PL/pgSQL. Он сохраняет старые значения из books и дату изменений в таблице books_log:

CREATE OR REPLACE FUNCTION save_changes()
    RETURNS TRIGGER
    LANGUAGE PLPGSQL
    AS
    $$
    BEGIN
        IF (NEW.book_name <> OLD.book_name) OR (NEW.price <> OLD.price) THEN
        INSERT INTO books_log(book_id,book_name,price,change_date)
        VALUES(OLD.id,OLD.book_name,OLD.price, now());
        END IF;

	RETURN NEW;
    END;
    $$

Когда функция PL/pgSQL вызывается триггером изменения данных, в блоке верхнего уровня автоматически создается несколько специальных переменных. Они перечислены в таблице ниже. В приведенном выше коде используются переменные NEW и OLD для доступа к новой и старой строке таблицы соответственно.

Переменные триггерной функции изменения данных PL/pgSQL
Имя Тип данных Описание

NEW

record

Новая строка таблицы для операций INSERT/UPDATE в триггерах уровня строки. Эта переменная имеет значение NULL в триггерах уровня оператора и для операций DELETE

OLD

record

Старая строка таблицы для операций UPDATE/DELETE в триггерах уровня строки. Эта переменная имеет значение NULL в триггерах уровня оператора и для операций INSERT

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)

Идентификатор объекта таблицы, вызвавшей срабатывание триггера

TG_TABLE_NAME

name

Имя таблицы, вызвавшей срабатывание триггера

TG_TABLE_SCHEMA

name

Имя схемы, содержащей таблицу, вызывающую срабатывание триггера

TG_NARGS

integer

Количество аргументов, передаваемых триггерной функции в операторе CREATE TRIGGER

TG_ARGV[]

Data type array of text

Аргументы оператора CREATE TRIGGER. Индекс считается от 0. Недопустимые индексы (меньше 0, превышающие или равные значению TG_NARGS) возвращают NULL

За дополнительной информацией обратитесь к статье Trigger functions.

Используйте команду CREATE TRIGGER, чтобы определить новый триггер. Чтобы создать или заменить триггер таблицы, пользователь должен иметь привилегию TRIGGER для этой таблицы, a также привилегию EXECUTE для функции триггера.

CREATE TRIGGER last_changes
  BEFORE UPDATE
  ON books
  FOR EACH ROW
  EXECUTE PROCEDURE save_changes();

Триггер last_changes создается как триггер BEFORE UPDATE уровня строки таблицы books. Он вызывает триггерную функцию save_changes. За дополнительной информацией о синтаксисе CREATE TRIGGER обратитесь к статье CREATE TRIGGER.

Обновите таблицу books, чтобы активировать триггер:

UPDATE books SET price = 52 WHERE ID = 3;

Проверьте содержание таблицы books_log:

SELECT * from books_log;

Результат:

 id | book_id | book_name | price |        change_date
----+---------+-----------+-------+----------------------------
  1 |       3 | 1984      |    20 | 2024-04-04 16:30:07.001503

Изменение триггера

Используйте команду ALTER TRIGGER, чтобы изменить триггер.

В следующем примере триггер переименовывается:

ALTER TRIGGER last_changes ON books RENAME TO last_changes_new_name;

Отключение триггера

Чтобы временно отключить или подключить триггер, вызовите команду ALTER TABLE как показано ниже.

Отключение триггера:

ALTER TABLE books DISABLE TRIGGER last_changes_new_name;

Подключение триггера:

ALTER TABLE books ENABLE TRIGGER last_changes_new_name;

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

Чтобы удалить триггер, используйте команду DROP TRIGGER:

DROP TRIGGER last_changes_new_name ON books;

Чтобы выполнить эту команду, текущий пользователь должен быть владельцем таблицы, для которой определен триггер.

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

Для вывода списка всех триггеров в текущей базе данных, используйте системное представление information_schema.triggers:

SELECT event_object_table, trigger_name FROM information_schema.triggers;

Результат:

 event_object_table | trigger_name
--------------------+--------------
 books              | last_changes

Также можно использовать системный каталог pg_trigger для вывода списка триггеров:

SELECT oid, tgrelid, tgparentid, tgname FROM pg_trigger;

Результат:

  oid   | tgrelid | tgparentid |    tgname
--------+---------+------------+--------------
 191005 |  190751 |          0 | last_changes

Особенности поведения триггеров

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

Триггер AFTER

Выполнение триггера AFTER может быть отложено до конца транзакции, а не до конца оператора, если триггер был определен как CONSTRAINT. Во всех случаях триггер выполняется как часть той же транзакции, что и оператор, вызвавший его, поэтому если оператор или триггер вызывают ошибку, действия обоих будут отменены.

Секционированные и родительские таблицы

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

Если UPDATE в секционированной таблице приводит к перемещению строки в другую секцию, это будет выполнено как операция DELETE из исходной секции с последующей операцией INSERT в новую секцию. В этом случае все триггеры BEFORE UPDATE и BEFORE DELETE на уровне строки сработают в исходной секции. Затем в целевой секции сработают все триггеры BEFORE INSERT на уровне строки. Когда все эти триггеры влияют на перемещаемую строку, это может привести к неожиданным результатам. Триггеры AFTER DELETE и AFTER INSERT на уровне строки сработают, но триггеры AFTER UPDATE не применятся, поскольку UPDATE был преобразован в DELETE и INSERT. Ни один из триггеров уровня операторов DELETE или INSERT не сработает. Применятся только триггеры UPDATE, определенные в целевой таблице оператора UPDATE.

Возвращаемые значения триггерных функций

Функции, вызываемые триггерами уровня операторов, должны возвращать NULL. Триггерные функции, вызываемые триггерами уровня строки, могут возвращать строку таблицы (значение типа HeapTuple). Триггер BEFORE на уровне строки может возвращать следующий результат:

  • NULL — пропуск операции для текущей строки. Это указывает PostgreSQL не выполнять операцию INSERT, UPDATE или DELETE для конкретной строки таблицы, которая вызвала триггер.

  • Строка таблицы — только для триггеров INSERT и UPDATE, возвращаемая строка будет вставлена или заменит обновляемую строку. Это позволяет триггерной функции вносить изменения в обновляемую или вставляемую строку.

Если вы не планируете использовать упомянутые выше опции с триггером BEFORE на уровне строки, его триггерная функция должна возвращать ту же строку, которая была передана — новую строку для триггеров INSERT и UPDATE, старую строку для триггеров DELETE.

Триггер INSTEAD OF на уровне строки должен либо возвращать NULL, чтобы указать, что он не изменял никаких данных из базовых таблиц представления, либо он должен возвращать переданную строку представления (новую строку для INSERT и UPDATE или старую строку для DELETE). Ненулевое возвращаемое значение сигнализирует, что триггер выполнил необходимые изменения данных в представлении. Это увеличит счетчик количества строк, на которые влияет команда. Только для операций INSERT и UPDATE триггерная функция может изменить новую строку перед её возвратом.

Возвращаемое значение игнорируется для триггеров уровня строки AFTER, поэтому они могут возвращать NULL.

Генерируемые столбцы

Генерируемые столбцы заслуживают особого внимания. Сохраненные генерируемые столбцы вычисляются после триггеров BEFORE и до триггеров AFTER. Сгенерированное значение можно проверить в триггерах AFTER. В триггерах BEFORE старая строка (переменная OLD) содержит старое сгенерированное значение, но новая строка еще не содержит нового сгенерированного значения. Изменения генерированного столбца в триггере BEFORE игнорируются и будут перезаписаны.

Порядок срабатывания триггеров

Если для одного и того же события в одном отношении определено более одного триггера, триггеры сработают в алфавитном порядке по имени триггера. В случае триггеров BEFORE и INSTEAD OF строка, возвращаемая каждым триггером, становится входными данными для следующего триггера. Если какой-либо триггер BEFORE или INSTEAD OF возвращает NULL, операция останавливается и последующие триггеры для этой строки не срабатывают.

Условие триггера WHEN

Определение триггера также может содержать логическое условие WHEN, которое проверяет, должен ли срабатывать триггер. В триггерах уровня строки условие WHEN может проверять старые и новые значения столбцов строк. Триггеры уровня оператора также могут включать в себя условия WHEN, хотя для них эта функция не так полезна. В триггере BEFORE условие WHEN оценивается непосредственно перед выполнением функции, поэтому использование WHEN существенно не отличается от проверки того же условия в начале функции триггера. Однако в триггере AFTER условие WHEN оценивается сразу после обновления строки и определяет, ставится ли событие в очередь для запуска триггера в конце оператора. Следовательно, если условие WHEN в триггере AFTER не возвращает true, нет необходимости ставить событие в очередь или повторно получать эту строку в конце оператора. Это может значительно ускорить выполнение операторов, изменяющих большое количество строк, с помощью триггера, который должен сработать только для нескольких. Триггеры INSTEAD OF не поддерживают условие WHEN.

Каскадные триггеры

Если триггерная функция выполняет команды SQL, эти команды могут снова запустить триггеры. Это поведение известно как каскадные триггеры. Прямого ограничения на количество каскадных уровней нет. Каскады могут вызывать рекурсивный вызов одного и того же триггера. Например, триггер INSERT может выполнить команду, которая вставляет дополнительную строку в ту же таблицу, вызывая повторное срабатывание триггера INSERT. Ответственность за предотвращение бесконечной рекурсии в таких случаях лежит на программисте.

Аргументы триггеров

При определении триггера вы можете указать аргументы. Целью включения аргументов в определение триггера является предоставление возможности различным триггерам со схожими требованиями вызывать одну и ту же функцию. Каждый язык программирования, поддерживающий триггеры, имеет свой собственный метод, позволяющий сделать входные данные триггера доступными для триггерной функции. Эти входные данные включают тип события триггера (например, INSERT или UPDATE) и аргументы, перечисленные в команде CREATE TRIGGER. Для триггеров уровня строки входные данные также включают новую строку для триггеров INSERT и UPDATE и старую строку для триггеров UPDATE и DELETE.

Видимость изменений данных

Если вы выполняете команды SQL в триггерной функции, эти команды SQL видят изменения данных в соответствии со следующими правилами видимости данных:

  • Ни одно из изменений, внесенных оператором, не видно триггерам уровня оператора BEFORE, но все изменения видны триггерам уровня оператора AFTER.

  • Изменение данных (вставка, обновление или удаление), вызывающее срабатывание триггера, не видно командам SQL, выполняемым в триггере BEFORE на уровне строки, поскольку оно еще не произошло.

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

  • Триггер INSTEAD OF на уровне строки может видеть последствия изменений данных, сделанных предыдущими вызовами триггера INSTEAD OF в той же команде.

  • Когда срабатывает триггер AFTER на уровне строки, все изменения данных, внесенные командой, уже завершены и видны в вызванной триггерной функции.

Если триггерная функция написана на любом из стандартных процедурных языков, приведенные выше утверждения верны, только если функция объявлена как VOLATILE. Функции, объявленные как STABLE или IMMUTABLE, не увидят изменений, внесенных вызывающей командой.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней