Триггеры

Сегодня познакомимся со специфическим видом процедур в SQL - триггерами.

Триггер - это объект БД, который запускает триггерную функцию в качестве реакции на какой-либо запрос.

Триггерная функция (триггерная процедура*) - SQL-функция. Мы познакомились с ними в прошлой статье. Но есть небольшой нюанс, о нем - в следующем разделе.

* Независимо от названия, де-факто это функция. По крайней мере, в PostgreSQL.

По сути, речь идет о функции, которая выполнится до, после или (при работе с view, в рамках статьи не затронем) вместо INSERT, UPDATE, DELETE или TRUNCATE запросов определенной таблицы.

Прежде чем перейдем к разбору синтаксиса, подчеркну два аспекта:

1. Нюансы работы с триггерами могут ощутимо различаться в разных СУБД. Как синтаксически, так и в плане ограничений к их применению;
2. Триггеры нужно использовать осторожно. Ваша основная зона ответственности как бэкенд-разработчика - именно уровень приложения. Не стоит пихать в триггеры бизнес-логику или какие-то неочевидные действия (или хотя бы документируйте их везде, где это возможно). Использование триггеров может быть более производительным, нежели выполнение тех же операций через Java-код. Но это размазывает ответственность между приложением и БД, затрудняя понимание и поддерживаемость продукта. В идеале, триггеры, если они есть, должны отвечать за вещи, не имеющие отношения к домену вашего приложения. Сбор статистики, наполнение view, логирование и т.д. И даже такие действия лучше документировать, чтобы не превращать приложение в коробку с непонятным черным колдунством.

Синтаксис

Любой триггер можно классифицировать на основании трех аспектов.

По оператору, к которому он привязан:

• INSERT;
• UPDATE;
• DELETE;
• TRUNCATE.

По моменту срабатывания:

• BEFORE;
• INSTEAD OF. Как говорил выше, в рамках статьи этот вариант не рассматриваем в силу узкой специфики;
• AFTER.

По способу применения:

• К оператору (FOR EACH STATEMENT). Иными словами, один раз на запрос, независимо от того, сколько строк этот запрос добавил/изменил/удалил. От нуля (т.е. сработает даже если де-факто запрос ничего не сделал) до всего содержимого;
• К строке (FOR EACH ROW). Т.е. вызов произойдет на каждую добавленную/измененную/удаленную строку.

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

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

Классические примеры для работы триггеров - таблица, в которой будут логироваться действия с другой таблицей.

Логировать будем  INSERT, UPDATE и DELETE у простенькой таблицы t1:

create table t1 (
  id bigint primary key,
  col bigint
);

Создадим таблицу-журнал:

create table t1_log (
  t1_id            bigint        not null,
  action_type      varchar(10)   not null,
  col_old_value    bigint,
  col_new_value    bigint,
  happened         timestamp     not null default now()
);

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

Напишем следующую функцию:

create or replace function log_t1() returns trigger
language plpgsql
as $$
  declare
    id bigint = (
      select case 
        when TG_OP = 'DELETE' then old.id
        else new.id 
      end
     );
  begin
    insert into 
      t1_log(t1_id, action_type, col_old_value, col_new_value)
      values (id, TG_OP, old.col, new.col);

    return new;
  end
$$;

Разберем моменты, которые, скорее всего, будут непонятны.

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

Далее вы можете заметить интересную конструкцию CASE в DELCARE-блоке. По сути, это аналог if-else из мира SQL. Именно SQL, а не PL/SQL - последний, как раз, использует более привычные if’ы, хоть их синтаксис и отличен от знакомого нам из Java. Подробнее о case можно почитать здесь: CASE в SQL. Суть использования этой конструкции здесь станет понятна в следующем абзаце.

Следующим непонятным моментов будет, вероятно TG_OP, NEW и OLD, которые содержат какие-то значения, но откуда он взялся - решительно непонятно. Это переменные триггеров, которые заполняются автоматически (за редким исключением) и могут быть использованы внутри триггерной функции. Подробнее можно почитать здесь: переменные триггеров.

Как вы можете увидеть в документации, для DELETE-запроса NEW будет NULL, что и стало причиной использования конструкции CASE в нашей функции.

В целом, рекомендую прочитать весь пункт 43.10.1 по ссылке выше. Обратить внимание стоит как минимум на две вещи (не считая описания упомянутых выше переменных):

1. Переменная TG_ARGV[] - она позволяет передавать в триггерную функцию собственные параметры, тем самым обходя ограничение на отсутствие параметров при определении функции.
2. Описание работы с возвращаемым значением из триггерной функции. Абзац с INSTEAD OF для нас не критичен, а вот оставшиеся два - вполне. Весьма важный пункт, который там хорошо описан, поэтому я не вижу смысла дублировать эту информацию в рамках статьи.

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

Осталось привязать эту функцию триггером к таблице.

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

create or replace trigger log_t1_insert_trigger
after insert on t1
for each row
execute procedure log_t1();

Как можете видеть, триггер тоже поддерживает CREATE OR REPLACE наравне с обычным CREATE.

После названия триггера указано, когда он сработает - AFTER операции INSERT для таблицы t1. Будет отрабатывать FOR EACH ROW и заключается в вызове функции (английский термин для триггерной функции - trigger procedure) log_t1().

Теперь осталось добавить такие же триггеры на UPDATE и DELETE. Но мы сделаем немного проще:

• Удалим добавленный триггер (обратите внимание, что он жестко привязан к конкретной таблице):

drop trigger log_t1_insert_trigger on t1;

• Добавим общий триггер на все интересующие нас операции:

create or replace trigger log_t1_trigger
after update or insert or delete on t1
for each row
execute procedure log_t1();

Теперь можем протестировать вставку, изменение и удаление строк в таблице t1 и посмотреть результат в t1_log:

insert into t1 values (1, 1), (2, 2), (3, 3);
delete from t1 where id = 1;
update t1 set col = 10 where id != 1;

Из основного синтаксиса проигнорированным остался только ALTER. Для триггеров (на нашем уровне изучения) доступно только переименование, с ним все просто:

alter trigger log_t1_trigger on t1 rename to log_t1_tg;

Кроме того, подчеркну ряд моментов, которые не были освещены выше.

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

Собственно, порядок вызова зависит от СУБД. Стандарт SQL регламентирует вызовы триггеров в порядке их добавления, но PostgreSQL отходит от стандарта и ориентируется на имя триггера - они выполняются в алфавитном порядке (точнее, в порядке естественной сортировки строк по возрастанию).

Также при создании триггеры можно привязать не просто к конкретной операции, а сузить их использование до следующих ситуаций:

• Выполнения логического условия с помощью оператора WHEN. В рамках него доступны переменные триггера (включая OLD и NEW), что позволяет вызывать триггеры только в заданных ситуациях, а не на каждый вызов оператора. Актуально, в основном, для FOR EACH ROW триггеров. Примеры: ссылка;
• Для UPDATE-триггеров доступно предложение OF, которое позволяет указать столбцы, которые должны быть изменены, чтобы триггер сработал. Пример: ссылка. 

С теорией на сегодня все!

Переходим к практике:

Задача 1

Реализуйте логирование действий для всех таблиц в тестовой БД в виде FOR EACH STATEMENT триггеров, которые будут писать в таблицу логов примененную операцию, таблицу, к которой была применена операция и дату срабатывания триггера.

Задача 2

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

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!