SQL триггер¶

SQL триггер (от англ. trigger — спусковой крючок) — это хранимая процедура особого типа, которая автоматически выполняется (срабатывает) при наступлении определённого события в базе данных. Триггеры являются частью серверной логики системы управления базами данных (СУБД) и предназначены для поддержания целостности данных, реализации бизнес-правил, аудита изменений и автоматизации действий, которые должны выполняться при модификации таблиц или представлений.

¶История и развитие

Концепция триггеров возникла в 1970-х годах в рамках развития реляционных баз данных и теории активных баз данных. Первые реализации появились в коммерческих СУБД в 1980-х годах, например, в Ingres и Sybase. Стандартизация триггеров была проведена в спецификации SQL:1999 (ISO/IEC 9075), которая определила синтаксис, типы событий и семантику выполнения. В дальнейшем стандарт уточнялся в версиях SQL:2003, SQL:2008 и SQL:2016, добавив поддержку триггеров INSTEAD OF для представлений и возможность каскадного срабатывания. Наибольшее развитие триггеры получили в СУБД Oracle, Microsoft SQL Server, PostgreSQL и MySQL, каждая из которых имеет собственные расширения и нюансы реализации.

¶Классификация триггеров

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

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

• BEFORE (или FOR EACH ROW BEFORE) — выполняется до начала операции модификации данных (INSERT, UPDATE, DELETE). Используется для проверки условий, изменения значений вставляемых или обновляемых данных, а также для предотвращения некорректных изменений.
• AFTER (или FOR EACH ROW AFTER) — выполняется после завершения операции модификации данных. Применяется для каскадных обновлений, записи в журналы аудита, синхронизации связанных таблиц.
• INSTEAD OF — выполняется вместо самой операции модификации. Чаще всего используется для представлений (VIEW), которые не поддерживают прямые операции INSERT, UPDATE, DELETE. Позволяет реализовать логику обновления базовых таблиц через представление.

¶По уровню срабатывания

• Строчные триггеры (ROW-level) — срабатывают один раз для каждой строки, затронутой операцией. Например, при UPDATE, изменяющем 100 строк, триггер выполнится 100 раз. Позволяют обращаться к значениям старой и новой строки через псевдопеременные (OLD и NEW).
• Табличные триггеры (STATEMENT-level) — срабатывают один раз для всей операции в целом, независимо от количества затронутых строк. Используются для выполнения действий, не зависящих от конкретных строк, например, для проверки бизнес-правил на уровне всей операции.

¶По типу события

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

¶По области действия

• Табличные триггеры — привязаны к конкретной таблице.
• Триггеры на представления — привязаны к представлению (VIEW) и обычно реализуются как INSTEAD OF.
• Системные триггеры — срабатывают на события уровня базы данных или сервера (например, LOGON, LOGOFF, DDL-события). Поддерживаются не всеми СУБД.

¶Устройство и синтаксис

Триггер состоит из следующих компонентов:

• Имя триггера — уникальное в пределах схемы базы данных.
• Событие — одна или несколько операций (INSERT, UPDATE, DELETE), на которые реагирует триггер.
• Время срабатывания — BEFORE, AFTER или INSTEAD OF.
• Уровень срабатывания — FOR EACH ROW или FOR EACH STATEMENT.
• Условие (WHEN) — необязательное логическое выражение, при истинности которого триггер выполняется. Позволяет фильтровать срабатывания.
• Тело триггера — блок кода на процедурном языке СУБД (PL/SQL в Oracle, T-SQL в MS SQL Server, PL/pgSQL в PostgreSQL, хранимые процедуры в MySQL). В теле триггера можно использовать псевдопеременные OLD (старое значение строки) и NEW (новое значение строки) для доступа к данным до и после операции.

Пример синтаксиса на SQL (PostgreSQL):

```sql CREATE OR REPLACE FUNCTION audit_employee_changes() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = 'INSERT' THEN INSERT INTO audit_log(table_name, action, old_data, new_data, changed_at) VALUES ('employees', 'INSERT', NULL, row_to_json(NEW), NOW()); ELSIF TG_OP = 'UPDATE' THEN INSERT INTO audit_log(table_name, action, old_data, new_data, changed_at) VALUES ('employees', 'UPDATE', row_to_json(OLD), row_to_json(NEW), NOW()); ELSIF TG_OP = 'DELETE' THEN INSERT INTO audit_log(table_name, action, old_data, new_data, changed_at) VALUES ('employees', 'DELETE', row_to_json(OLD), NULL, NOW()); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_employees AFTER INSERT OR UPDATE OR DELETE ON employees FOR EACH ROW EXECUTE FUNCTION audit_employee_changes(); ```

¶Применение

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

¶Обеспечение целостности данных

• Каскадные обновления и удаления — автоматическое изменение или удаление связанных записей в дочерних таблицах при изменении родительской записи. Например, при удалении клиента автоматически удаляются все его заказы.
• Проверка ограничений — реализация сложных бизнес-правил, которые невозможно выразить стандартными ограничениями (CHECK, UNIQUE, FOREIGN KEY). Например, запрет на изменение зарплаты сотрудника более чем на 20% за одну операцию.
• Защита от некорректных данных — автоматическая корректировка вводимых значений (например, приведение строк к верхнему регистру, округление чисел).

¶Аудит и логирование

Триггеры позволяют фиксировать все изменения данных в отдельной таблице аудита: кто, когда, что и как изменил. Это важно для соответствия требованиям законодательства (например, 152-ФЗ «О персональных данных» в России) и для расследования инцидентов.

¶Реализация бизнес-логики

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

¶Ограничение доступа

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

¶Ограничения и критика

Несмотря на полезность, триггеры имеют ряд недостатков:

• Скрытая логика — триггеры выполняются неявно, что может затруднить отладку и понимание поведения базы данных разработчиками, не знакомыми с их наличием.
• Снижение производительности — каждый триггер добавляет накладные расходы на операции модификации данных. Особенно критично для строчных триггеров при массовых операциях (например, UPDATE 100 000 строк).
• Каскадное срабатывание — триггеры могут вызывать другие триггеры, что приводит к цепочкам выполнения, сложным для контроля и способным вызвать рекурсию или ошибки.
• Проблемы с транзакциями — триггеры выполняются в контексте той же транзакции, что и основная операция. Ошибка в триггере приводит к откату всей транзакции, что может быть нежелательно.
• Ограниченная переносимость — синтаксис и возможности триггеров сильно различаются между СУБД, что затрудняет миграцию приложений.

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

¶Интересные факты

• В стандарте SQL:1999 триггеры были впервые включены как обязательный элемент, но на практике каждая СУБД реализовала их по-своему, что привело к значительным различиям.
• В PostgreSQL триггеры могут быть написаны на нескольких языках: PL/pgSQL, PL/Python, PL/Perl, PL/Tcl и других.
• Некоторые СУБД (например, Oracle) поддерживают составные триггеры, которые объединяют несколько временных точек (BEFORE STATEMENT, BEFORE ROW, AFTER ROW, AFTER STATEMENT) в одном объекте.
• В MySQL триггеры поддерживаются только для таблиц, использующих движки InnoDB и MySQL Cluster, и не поддерживают триггеры на уровне операторов (только строчные).
• В Microsoft SQL Server триггеры могут быть рекурсивными (срабатывать сами на себя) при определённых настройках, что требует осторожного проектирования.