Что такое триггер в базе данных

Триггер — это PL/SQL блок, который хранится в базе данных и срабатывает (выполняется) в ответ на указанное событие.

Триггер может быть определён на таблицу, представление, схему (владельца схемы) или базу данных (всех пользователей).

Типы событий триггеров:

DML-команда ( DELETE , INSERT или UPDATE ).
DDL-команды ( CREATE , ALTER или DROP ).
Операции базы данных, такие как SERVERERROR , LOGON , LOGOFF , STARTUP или SHUTDOWN .

Пример создания триггера:

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

Триггеры делятся на:

Триггеры базы данных. Срабатывают при возникновении DML, DDL или системного события в схеме или базе данных.
Триггеры приложений. Срабатывают при возникновении события в конкретном приложении.

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

Безопасности.
Аудита.
Целостности данных.
Ссылочной целостности.
Репликации (копирования) таблиц.
Автоматического вычисления производных данных.
Ведения журнала событий.

Допустимые типы триггеров:

Простые DML-триггеры ( BEFORE , AFTER , INSTEAD OF (только для view)).
Составные (compound) триггеры.
Не DML-триггеры (DDL-события, события базы данных).

Уровня команды (по умолчанию). Срабатывают один раз на событие триггера, даже тогда, когда не затронута ни одна из строк.
Строковыми. Нужно прописать FOR EACH ROW , срабатывают один раз на каждую затронутую строку по событию триггера, не срабатывают, если событие не затронуло ни одной строки.

3. Использование условных предикатов

Можно так же задавать условия для выполнения триггеров.

В теле триггера можно использовать конструкции (если триггер состоит из нескольких DML-операций):

IF DELETING ,
IF UPDATING ,
IF INSERTING ,

чтобы определить, какое событие вызвало триггер, который слушает несколько событий.

Можно так же использовать WHEN для строковых триггеров.

О NEW и OLD (доступны только в строковых триггерах):

DML-команда	OLD	NEW
INSERT	NULL	введённое значение
UPDATE	значение до обновления	значение после обновления
DELETE	удаляемое значение	NULL

Краткое описание модели выполнения триггера:

Выполняются все BEFORE триггеры уровня команды.

Для каждой строки, затронутой триггером:

Выполняются все строковые BEFORE триггеры
Выполняется DML-команда и проверка ограничений целостности.
Выполняются все строковые AFTER триггеры.

У триггера есть 2 состояния: ENABLE и DISABLE . Синтаксис:

4. Тестирование триггеров

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

Информацию о триггерах можно посмотреть в словарях USER_OBJECTS , USER/ALL/DBA_TRIGGERS . Синтаксис ошибок триггеров можно посмотреть в словаре USER_ERRORS .

5. Составные (compound) триггеры

Составной (compound) триггер — это один триггер на таблицу, позволяющий задать действия для каждой из следующих четырёх точек синхронизации:

Общие сведения о триггерах

где plsql_trigger_source, это такая конструкция:

Конструкции simple_dml_trigger, instead_of_dml_trigger, compound_dml_trigger и system_trigger будут приведены в соответствующих разделах статьи.

DML triggers

DML триггеры создаются для таблиц или представлений, срабатывают при вставке, обновлении или удалении записей.
Триггер может быть создан в другой схеме, отличной от той, где определена таблицы. В таком случае текущей схемой при выполнении триггера считается схема самого триггера.
При операции MERGE срабатывают триггеры на изменение, вставку или удаление записей в зависимости от операции со строкой.
Триггер – часть транзакции, ошибка в триггере откатывает операцию, изменения таблиц в триггере становятся частью транзакции.
Если откатывается транзакция, изменения триггера тоже откатываются.
В триггерах запрещены операторы DDL и управления транзакциями (исключения – автономные транзакции).

Конструкция simple_dml_trigger:

Где, dml_event_clause:

referencing_clause:

trigger_edition_clause:

trigger_body:

На таблице
На представлении (instead of trigger)

Вставка записей (insert)
Обновление записей (update)
Удаление записей (delete)

Уровень всей команды (statement level triggers)
Уровень записи (row level triggers)
Составные триггеры (compound triggers)

Перед выполнением операции (before)
После выполнения операции (after)

Crossedition triggers — служат для межредакционного взаимодействия, например для переноса и трансформации данных из полей, отсутствующих в новой редакции, в другие поля.

Условные предикаты для определения операции, на которую сработал триггер:

Предикат	Описание
Inserting	True, если триггер сработал на операцию Insert
Updating	True, если триггер сработал на операцию Update
Updating(‘colum’)	True, если триггер сработал на операцию Update, которая затрагивает определенное поле
Deleting	True, если триггер сработал на операцию Delete

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

Псевдозаписи

Существуют псевдозаписи, позволяющие обратиться к полям изменяемой записи и получить значения полей до изменения и значения полей после изменения. Это записи old и new. С помощью конструкции Referencing можно изменить их имена. Структура этих записей tablename%rowtype. Эти записи есть только у триггеров row level или у compound триггеров (с секциями уровня записи).

Операция срабатывания триггера	OLD.column	NEW.column
Insert	Null	Новое значение
Update	Старое значение	Новое значение
Delete	Старое значение	Null

С псевдозаписями запрещены операции уровня всей записи ( :new = null;)
Нельзя изменять значения полей записи old
Если триггер срабатывает на delete, нельзя изменить значения полей записи new
В триггере after нельзя изменить значения полей записи new

Instead of dml triggers

Создаются для представлений (view) и служат для замещения DML операций своим функционалом.
Позволяют производить операции вставки/обновления или удаления для необновляемых представлений.

Конструкция instead_of_dml_trigger:

Это всегда триггер уровня записи (row level)
Имеет доступ к псевдозаписям old и new, но не может изменять их
Заменяет собой dml операцию с представлением (view)

Instead of triggers on Nested Table Columns of Views

Можно создать триггер для вложенной в представлении таблицы. В таком триггере также присутствует дополнительная псевдозапись – parent, которая ссылается на всю запись представления (стандартные псевдозаписи old и new ссылаются только на записи вложенной таблицы)

Составные DML триггера (compound DML triggers)

Появившиеся в версии 11G эти триггера включают в одном блоке обработку всех видов DML триггеров.
Конструкция compound_dml_trigger:

Где, compound_trigger_block:

timing_point_section:

timing_point:

tps_body:

Срабатывают такие триггера при разных событиях и в разные моменты времени (на уровне оператора или строки, при вставке/обновлении/удалении, до или после события).
Не могут быть автономными транзакциями.

Собирать в коллекцию строки для вставки в другую таблицу, чтобы периодически вставлять их пачкой
Избежать ошибки мутирующей таблицы (mutating-table error)

Структура составного триггера

Before statement
After statement
Before each row
After each row

Нельзя обращаться к псевдозаписям old, new или parent в секциях уровня выражения (before statement и after statement)
Изменять значения полей псевдозаписи new можно только в секции before each row
Исключения, сгенерированные в одной секции, нельзя обрабатывать в другой секции
Если используется оператор goto, он должен указывать на код в той же секции

Основные правила определения DML триггеров

Update of – позволяет указать список изменяемых полей для запуска триггера
Все условия в заголовке и When … проверяются без запуска триггера на стадии выполнения SQL
В операторе When можно использовать только встроенные функции
Можно делать несколько триггеров одного вида, порядок выполнения не определен по умолчанию, но его можно задать с помощью конструкции FOLLOWS TRIGGER_FIRST
Ограничения уникальности проверяются при изменении записи, то есть после выполнения триггеров before
Секция объявления переменных определяется словом DECLARE
Основной блок триггера подчиняется тем же правилам, что и обычные PL/SQL блоки

Ограничения DML триггеров

нельзя выполнять DDL statements (только в автономной транзакции)
нельзя запускать подпрограммы с операторами контроля транзакций
не имеет доступа к SERIALLY_REUSABLE пакетов
размер не может превышать 32К
нельзя декларировать переменные типа LONG и LONG RAW

Ошибка мутирования таблицы ORA-04091

использовать триггеры уровня операции
автономная транзакция в триггере
использовать сторонние структуры (коллекции уровня пакета)
использовать COMPOUND TRIGGER
изменение самого алгоритма с выносом функционала из триггера

Системные триггеры (System triggers)

Конструкция system_trigger:

Такие триггеры относятся или к схеме, или ко всей базе данных.

Триггеры: хороший, плохой, злой.

Триггеры — важный и удобный элемент в наборе инструментов систем управления баз данных.
Про триггеры привычно рассуждать в контексте реляционных баз данных, но и в NoSql-базах они тоже есть: в MongoDb, Neo4j, а в есть Redis Keyspace Notifications.

В дальнейшем разговор пойдет в основном о Postgresql. Best Practice для других баз могут отличаться.

Под триггером принято понимать специально оформленную функцию, автоматически запущенную СУБД по некоторому событию.
В реляционных базах триггеры связаны с событиями модификации данных — INSERT, UPDATE, DELETE, а в MongoDb есть еще и события на аутентификацию и события по расписанию.
Чтобы функция была вызвана автоматически, она должна быть выражена в виде хранимой процедуры, чтобы была возможность запустить ее в контексте сервера базы данных, в котором произошло целевое событие.

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

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

Первый подход довольно часто практикуется при эксплуатации ORACLE и MSSQL. Так можно сделать и в Postgresql, особенно учитывая богатый набор языков для реализации хранимых процедур.
У этого подхода есть два узких места — хранимые процедуры очень сложно тестировать и при увеличении количества данных СУБД должна уметь поддерживать работу хранимых процедур поверх данных, распределенных по нескольким физическим серверам. Postgresql и Mysql такой возможностью похвастаться не могут, поэтому если вы разрабатываете проект с этими базами, постарайтесь избежать хранения бизнес-логики в СУБД.
Так же отдельной болью будет версионирование хранимых процедур и связанные с этим бэкапы.
Отдельную проблему, которую придется решать, это взаимодействие с внешними сервисами из базы (отправить письмо, сходить в процессинг, сохранить файл).
К плюсам можно отнести скорость выполнения хранимых процедур. Операции, выполненные рядом с данными будут кратно быстрее этих же операций, выполненных в приложении. Основная причина — network issues.
Кроме того, данный подход позволяет соединить СУБД и HTTP-сервер в одну сущность, используя, например, PostgRest.

При втором подходе в СУБД нет никаких хранимых процедур, и основной задачей базы в данном случае является консистентно сохранить данные. Эта задача реализуется декларативной настройкой ограничений (constraints), таких, как ограничения на NOT NULL, внешними ключами, CHECK-правилами и DOMAIN-типами. Кроме того, процессы бизнес-логики со стороны приложения должны быть оформлены в транзакции, чтобы избежать проблем с консистентностью.
К плюсам можно отнести хорошую тестируемость, потенциальную возможность шардирования (зависит от схемы, не всегда получится сохранить консистентность), удобное версионирование на стороне приложения, понятные миграции и бэкапы.
В прошлом пункте к плюсам хранимых процедур была отнесена скорость работы. Операции в приложении всегда будет работать медленнее, чем операции в базе, но можно найти баланс. Если в пределах одного процесса бизнес-логики нам требуется прочитать-обновить-сохранить данные без взаимодействия с внешними сервисами, нам на помощь придут Common Table Expressions. Это несколько SQL-операторов, которые могут быть выполнены последовательно, и результат работы одного может быть источником данных для другого. Все эти операторы выполняются в пределах одной транзакции, и дают возможность получить скорость работы сравнимой со скоростью хранимой процедуры. У этого подхода есть важное ограничение — воспользоваться можно только SQL-синтаксисом.

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

Есть шутка что большие проекты, обычно, находятся в двух состояниях — переписывания всего кода приложения на процедуры или переписывания всех процедур в код приложения 🙂

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

Итак, события генерируются на INSERT, UPDATE и DELETE до (BEFORE) и после (AFTER) соответствующей операции.

Как правило, функции, запускающиеся до (BEFORE) операции используются для валидации данных. Если по какой-то причине при проектировании вам не хватило констрейнтов и прав на таблицы, то в функции, запускаемой до изменения данных можно провалидировать операцию и прервать ее при необходимости.

Пример №1.
Предположим, что вы делаете подсистему обработки платежей и есть задача ограничить платежи в определенной валюте в зависимости от страны банка, выдавшего карту.

CREATE TABLE payments (
…
bank_country_id SMALLINT NOT NULL REFERENCES country(id),
currency_id SMALLINT NOT NULL REFERENCES currency(id),
…
);
CREATE TABLE currency2country ( …
bank_country_id SMALLINT NOT NULL REFERENCES country(id),
currency_id SMALLINT NOT NULL REFERENCES currency(id),
…
);

Перед вставкой в таблицу payments можно сделать триггер, в котором проверить существует ли запись в таблице currency2country, и если нет, прервать вставку.

CREATE OR REPLACE FUNCTION payments_before_insert_update() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (SELECT * FROM currency2country WHERE bank_country_id = NEW.bank_country_id AND currency_id = NEW.currency_id ) THEN
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Это плохой триггер.

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

В данном случае лучше поступить следующим образом:

CREATE TABLE currency2country ( …
bank_country_id SMALLINT NOT NULL REFERENCES country(id),
currency_id SMALLINT NOT NULL REFERENCES currency(id),
UNIQUE (bank_country_id, currency_id),
… );

CREATE TABLE payments ( …
bank_country_id SMALLINT NOT NULL,
currency_id SMALLINT NOT NULL,
FOREIGN KEY (bank_country_id, currency_id) REFERENCES currency2country (bank_country_id, currency_id),
… );

Результат тот же, но никаких триггеров и процедур.

Пример №2.
Так же триггер может применяться для валидации enum-полей, в которых предполагается некоторая последовательность изменения значений. Например, статус платежа может меняться new->finished/canceled/error, но никогда не может смениться с error на finished. Вот как это сделано в одной из реализаций Finite-State Machine на триггерах. Это довольно мудреный подход, ценность которого придется донести до всех участников команды разработки.

Можно ли решить эту проблему без триггеров?
Первый вариант подойдет, если flow изменений линеен (как в примере выше) и не предполагается, что он может быть изменен. Мы можем учесть порядок смены статусов в запросе:

UPDATE payment SET status = ‘finished’ WHERE status<’finished’ ….

У enum-типа в Postgresql есть свойство, о котором не все знают: значения enum-типа сортируются по порядку их объявлений. Поэтому

CREATE TYPE payment_status AS ENUM (‘new’,’finished’,’canceled’,’error’);
SELECT
‘new’ :: payment_status < ‘finished’ :: payment_status,
‘canceled’ :: payment_status < ‘finished’ :: payment_status
;

вернет true, false;
Таким образом мы можем быть уверены, что статус платежа не сменится на “предыдущий”.

Второй вариант подойдет в том случае, если смена статусом может быть нелинейной. Подход близок к Finite State Machine, указанному выше, но без триггеров. Необходимо создать функцию check_next_payment_status, которая знает о всех возможных последовательностях изменения статусов и принимает в качестве параметров текущий статус и новый, а возвращает true/false.
Тогда запрос будет выглядеть так:

UPDATE payment SET status = ‘finished’ WHERE check_next_payment_status(status,’finished’) ….

Третий вариант — перечислить все возможные варианты смены статуса в самом запросе:

UPDATE payment SET status = ‘finished’ WHERE status IN (‘new’, …) ….

Четвертый вариант — иметь 2 поля — status и status_previous и всю логику смены перенести в CHECK таблицы:

CREATE TABLE payments ( …
status payment_status NOT NULL,
status_previous payment_status NOT NULL,
CHECK (
(status = ‘finished’ AND status_previous = ‘new’)
OR
(status = ‘error’ AND status_previous = ‘new’)
…
)
…
);

А обновление делать так:

UPDATE payment SET status_previous = status, status = ‘finished’ ….

“Так ведь разработчик рано или поздно забудет про такой подход, а новый сотрудник может этого никогда и не узнать. Да и вообще, выглядит это как-то так…”, — можете сказать вы. И будете правы!

Но скрытие логики в триггерах или в более навернутых конечных автоматах может привести к некому аналогу неуточняемого поведения. Что будет, если один из разработчиков поменяет flow внутри триггера? Прекрасно, если существуют тесты, покрывающие все переходы. А если таких тестов нет или они неполны?

К сожалению, в Postgresql нет встроенного декларативно описываемого типа с конечными автоматами. Поэтому наиболее безопасным мне кажется третий вариант — перечислить все возможные варианты смены статуса в самом запросе.
Такой вариант можно надежно проверить статическим анализатором и быть уверенным в том, что все кейсы покрыты, даже если enum-тип поменяется со временем.
Но все перечисленные варианты выражены явно, что хорошо скажется на поддерживаемости кода.

Пример №3.
Еще распространенный вариант использования BEFORE-триггеров — это реализация в Postgresql функции UPSERT. Эта функция вставляет данные, но если данные по такому первичному ключу уже существуют, они заменяются на новые. В Postgresql нет такой функции и ее раньше часто реализовывали через триггер вида:

CREATE OR REPLACE FUNCTION payments_before_insert() RETURNS TRIGGER AS $$
BEGIN
IF EXISTS (SELECT * FROM payments WHERE bank_country_id = NEW.bank_country_id ) THEN
UPDATE payments SET bank_country_id = NEW.bank_country_id, counter = counter+NEW.counter WHERE bank_country_id = NEW.bank_country_id;
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Если вы обновляли Posgresql хотя бы раз за последние 4 года, то знайте, что начиная с версии 9.5 появился механизм ON CONFLICT DO UPDATE, который позволяет больше так не делать 🙂

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

А что же с AFTER-триггерами?
Им можно дать небольшое послабление 🙂

Поначалу кажется, что AFTER-триггеры не могут повлиять на бизнес-логику. Но нет, могут. Любой тип триггрера может иметь side-эффекты, или иными словами, может изменить данные в любой таблице, не только в целевой.

Бывает, что в AFTER-триггере разработчик обновляет статусы в связанной таблице. Например, платеж переходит в статус finished, и заказ, к которому он привязан должен перейти в статус paid. И да, нам хотелось бы сделать это в пределах одной транзакции.
Сделайте это в Common Table Expressions! Эта логика будет всегда под рукой и всегда лежать рядом с кодом приложения.

Представьте, что требования были такими, что к одному заказу всегда привязывался только один платеж. А потом они поменялись. Оплатить можно несколькими способами и статус ‘paid’ должен выставится только после завершения ВСЕХ платежей. И если эта логика будет лежать в самом запросе, вы сэкономите много бессонных ночей себе и денег компании.

Еще в AFTER-триггерах некоторые очень любят обновлять накопительную статистику. Например, количество платежей и сумму по клиенту в отдельной аггрегацционной таблице. Та же история — все в Common Table Expressions!

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

Это злой триггер.

“Какой-то дикий запад! Если тут приличные триггеры?”

Да, есть несколько заповедей хорошего триггера.
— это AFTER-триггер
— внутри триггера не делаются селекты
— внутри триггера не делаются апдейты
— внутри триггера не делаются запросы за пределы сервера
— внутри триггера не используются рекомендательные блокировки

“И зачем нужен такой триггер? Он ничего не умеет”
Умеет. Хороший триггер оперирует только данными, полученными в объектах OLD и NEW.

“И что он может с этим сделать?”
Из триггера можно сохранить лог.
Вернемся к примеру нелинейного изменения статусов. Обновляя статус в таблице вы теряете исторические данные о всех предыдущих значениях статуса. Иметь историю изменений очень полезно для последующего анализа работы системы — задержек между сменой статусов, путей попадания в нестандартные ситуации. Можно сохранять разницу между предыдущим и текущим значением числового поля.
Создаем отдельную таблицу и пишем туда информацию из основной.

Для того чтобы не сильно страдать с различными структурами таблиц, можно сделать все структуры похожими — идентификатор из основной таблицы, дата создания записи и JSON-объект отображающий всю записываемую строку.

“Лог? И это все?”
Все. Если ваша бизнес-логика находится в приложении, то это все.

Использование триггеров в СУБД MySQL

Триггер (англ. trigger) — это хранимая откомпилированная SQL-процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события внутри базы данных (вставки, удаления, обновления записей). Поддержка триггеров в MySQL началась с версии 5.0.2

Хранимые процедуры запускают во всех средах, и нет необходимости перестроения логики. С того момента как вы создали хранимую процедуру, не важно какое приложение вы используете для вызова процедуры. Также не важно на каком языке вы программируете, логика процедуры содержится на сервере БД.

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

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

Общий вид синтаксиса для создания триггера:

Где
trigger_name — название триггера;
trigger_time — время срабатывания триггера. BEFORE — перед событием. AFTER — после события;
trigger_event — событие:

insert — событие возбуждается операторами insert, data load, replace;
update — событие возбуждается оператором update;
delete — событие возбуждается операторами delete, replace. Операторы DROPTABLE и TRUNCATE не активируют выполнение триггера;
tbl_name — название таблицы;
trigger_body — выражение, которое выполняется при активации триггера.

Триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления».

Пример: создадим две таблицы test и log, напишем триггер, который после добавления каждой записи в 1-ю таблицу будет вести лог этого события:

Здесь оператор DELIMITER служит для определения знака начала/окончания процедуры и может состоять более, чем из одного символа (необходимо выбирать разделитель, который не будет использоваться в процедуре).

Что такое триггер в базе данных