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

В этой статье мы рассмотрим основы настройки триггеров в PostgreSQL и несколько примеров, чтобы увидеть, как их можно использовать. Триггеры могут быть созданы для изменений данных или событий базы данных. Мы рассмотрим изменения данных, т. е. триггеры, которые выполняются ДО/ПОСЛЕ операций INSERT, UPDATE, DELETE в таблице.

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

Согласно документации, триггер — это спецификация, согласно которой база данных должна автоматически выполнять определенную функцию всякий раз, когда выполняется определенный тип операции. Мы можем использовать триггеры для таблиц (разделенных или нет), представлений и внешних таблиц.

Триггеры можно определить для выполнения до или после любой операции INSERT, UPDATE или DELETE, либо один раз для измененной строки, либо один раз для оператора SQL.

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

ПРИМЕЧАНИЕ. Триггерная функция получает свои входные аргументы через определенную переменную, встроенную в PostgreSQL. Нам не нужно передавать какие-либо аргументы функции в определении.

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

Существует множество вариантов того, как вызывается триггер. Давайте посмотрим на них:

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

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

Например, если одна транзакция обновляет тысячу строк, триггер уровня оператора сработает только один раз, а триггер уровня строки сработает тысячу раз.

Триггеры также классифицируются в зависимости от того, срабатывают ли они до, после или вместо операции. Они называются триггерами BEFORE, триггерами AFTER и триггерами INSTEAD OF соответственно.

Для построчных триггеров:

• Возвращаемое значение игнорируется для триггеров уровня строки, запущенных ПОСЛЕ операции, поэтому они могут возвращать NULL.
• Триггер уровня BEFORE может возвращать NULL, чтобы пропустить операцию для текущей строки. Это предписывает исполнителю не выполнять операцию на уровне строки, которая вызвала триггер (вставка, изменение или удаление конкретной строки таблицы).
• Для триггеров BEFORE INSERT/UPDATE уровня строки возвращаемая строка становится строкой, которая будет вставлена ​​или обновлена. Это позволяет функции триггера изменять вставляемую или обновляемую строку.
• Триггерные функции, вызываемые триггерами для каждого оператора, всегда должны возвращать NULL.

ПРИМЕЧАНИЕ. Триггеры в транзакции выполняются в одной и той же транзакции (ДО или ПОСЛЕ). Выполнение отката для этих X-транзакций означает, что все изменения триггера будут отменены в тех же X-транзакциях.

Триггерная функция

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

Специальные локальные переменные с именем TG_something автоматически определяются для описания условия, вызвавшего вызов.

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

При использовании функции PL/pgSQL в качестве триггера в блоке верхнего уровня автоматически создаются некоторые переменные.

Обратите внимание, что функция должна быть объявлена ​​без аргументов, даже если она ожидает получить некоторые аргументы, указанные в CREATE TRIGGER.

Такие аргументы передаются через TG_ARGV.

Мы рассмотрим некоторые из наиболее распространенных:

NEW

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

OLD

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

TG_WHEN

Строка BEFORE, AFTER или INSTEAD OF, в зависимости от определения триггера.

TG_LEVEL

Указывает, является ли триггер уровнем ROW или STATEMENT.

TG_OP

Показывает операцию, вызвавшую срабатывание триггера. Может быть INSERT, UPDATE, DELETE или TRUNCATE.

TG_ARGV[]

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

2 шага для написания триггера в PostgreSQL

Полностью функционирующий триггер в PostgreSQL можно создать всего за два шага:

1. Определить триггерную функцию
2. Создайте триггер, используя эту функцию триггера

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

CREATE OR REPLACE FUNCTION function_name()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
DECLARE
  -- declare variables if needed
BEGIN
  -- function body goes here
END;
$$;

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

CREATE OR REPLACE TRIGGER name
  { BEFORE | AFTER } { event }
  ON table_name
  [ FOR [ EACH ] { ROW | STATEMENT } ]
  [ WHEN ( condition ) ]
  EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

CREATE OR REPLACE TRIGGER либо создаст новый триггер, либо заменит существующий триггер. Если вы этого не хотите, используйте вместо этого CREATE TRIGGER. Он не создаст триггер, если он уже существует.

Триггер будет связан с указанной таблицей и будет выполнять указанную функцию триггера при выполнении определенных операций над этой таблицей ДО/ПОСЛЕ выполнения операции.

ПРИМЕЧАНИЕ. Одну и ту же функцию триггера можно использовать для создания нескольких триггеров, если она написана правильно.

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

В триггерах уровня строки условие WHEN может проверять старые и/или новые значения столбцов строки.

Это бесполезно в случае триггеров уровня оператора, поскольку они не могут ссылаться ни на какие значения в таблице.

ПРИМЕЧАНИЕ. Если для одного и того же события определено несколько триггеров одного типа, они будут срабатывать в алфавитном порядке по имени.

Некоторые варианты использования для разных триггеров

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

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

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

Причина? Триггер AFTER всегда видит конечное значение строки, а триггер BEFORE может не видеть. После него могут срабатывать другие триггеры BEFORE.

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

ПРИМЕЧАНИЕ. Если функция триггера выполняет команды SQL, эти команды могут снова запускать триггеры. Это известно как каскадные триггеры . Прямого ограничения на количество уровней каскада нет.

Каскады могут вызвать рекурсивный вызов одного и того же триггера.

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

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

Видимость данных в триггерах

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

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

Журналы аудита, проверки ограничений и история версий — вот некоторые из распространенных и простых вариантов использования.

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

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

Примеры

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

Проверка ограничений

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

Этого можно добиться с помощью триггера BEFORE INSERT.

CREATE OR REPLACE FUNCTION name_null_checker() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.name IS NULL THEN
        RAISE EXCEPTION 'Name cannot be null';
    END IF;
    RETURN NEW;
END;
$$;

Мы написали базовую триггерную функцию, которая проверяет, является ли имя книги нулевым, и выдает исключение, если это так.

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

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

Теперь давайте создадим триггер, который будет срабатывать ПЕРЕД операцией INSERT для таблицы books.

CREATE TRIGGER name_null_checker_trigger BEFORE INSERT ON books
FOR EACH ROW EXECUTE FUNCTION name_null_checker();

И теперь у нас есть базовый триггер для выполнения проверки ограничений для таблицы books.

Ведение журнала аудита

Журналы аудита — это способ отслеживать изменения в таблице. Мы настроим триггер для регистрации всех операций INSERT, UPDATE и DELETE в таблице books.

Нам нужно создать таблицу аудита книг для хранения журналов аудита. Давайте сначала создадим его:

CREATE TABLE IF NOT EXISTS books_audit_store (
  name VARCHAR(128) UNIQUE NOT NULL,
  price float(2) NOT NULL,
  rating INT NOT NULL,
  operation VARCHAR(64) NOT NULL,
  modified_at TIMESTAMP NOT NULL
);

Теперь, когда у нас есть books_audit_storeтаблица, мы можем создать триггерную функцию, которая будет выполнять команду INSERT SQL:

CREATE OR REPLACE FUNCTION audit_logger()
  RETURNS trigger
  LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO books_audit_store (name, price, rating, operation, modified_at) 
    VALUES (NEW.name, NEW.price, NEW.rating, TG_OP, now());
  RETURN NEW;
END;
$$;

Наконец, создаем триггер для таблицы books_audit_store:

CREATE TRIGGER audit_logger_trigger
AFTER UPDATE ON books
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION audit_logger();

books таблица до обновления:

Мы обновили название книги «Scrum» на «Scrum-мастер»:

В результате обновления таблицы books другим значением мы получили запись в журнале аудита в books_audit_store таблице:

Триггер сброса

Триггер можно сбросить, выполнив оператор DROP TRIGGER.

DROP TRIGGER triggerName ON tableName;

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

Мы можем добавить IF EXISTSк оператору удаление триггера, только если он существует:

DROP TRIGGER IF EXISTS triggerName ON tableName;

ПРИМЕЧАНИЕ. В стандарте SQL имена триггеров не являются локальными для таблиц, поэтому вам не нужно указывать имя таблицы в операторе DROP TRIGGER.

Заключение

Это только введение в триггеры и то, как вы можете использовать их с функциями для создания постпроцессоров, валидаторов и регистраторов аудита.

Возможности безграничны, изучение официальной документации CREATE TRIGGER было бы хорошим следующим шагом. В нем есть несколько важных заметок и примеров, которые вы можете изучить.