Триггеры в базах данных: что это такое и как они работают

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

Где сидят триггеры? 

Представьте, вы владелец интернет-магазина продуктов питания. В ассортименте — тысячи позиций и наименований. Чтобы не запутаться, всегда знать остаток и специфику товара, вы ведете базу данных. Для удобства в ней собрана информация о продукте, например срок годности, остаток, артикул. 

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

Чтобы СУБД взаимодействовала с базами данных, запросы от нее к базе пишут на специальных языках, например SQL. Триггеры (triggers) — одна из его сущностей.

Для чего используется триггер? 

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

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

Триггер создают, чтобы: 

• проверять целостность данных. Триггер проверяет каждую введенную команду, насколько она достоверна и реальна. Эта процедура помогает избежать ошибок в базе данных; 
• откатывать ошибочные транзакции. То есть триггер обнаруживает несоответствие, а затем запрещает вносить изменения данных; 
• ограничивать реализацию значений внутри базы данных. С помощью триггера можно поставить ограничения на значения, которые нельзя вносить в таблицу. Так получится сохранить данные и соблюсти правила внутри таблицы; 
• собирать статистику. Триггеры могут фиксировать и копить информацию о действиях пользователей. А эти собранные данные можно в дальнейшем анализировать; 
• оптимизировать запросы. Триггер создает представление (materialized views) на основе данных из таблицы. Эта процедура нужна для того, чтобы ускорить обработку запросов и избежать повторного выполнения сложных операций над большими объемами данных.

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

• команда на изменения. В таком случае инструмент запускается при попытке изменения данных с помощью команды UPDATE;
• команда на вставку. Запуск происходит при попытке вставки данных с помощью команды INSERT;
• команда на удаление. Триггер активируется при попытке удаления данных с помощью команды DELETE.

При работе с триггерами используются две специальные таблицы: таблица вставок (INSERTED) и таблица удалений (DELETED). Причем структура этих файлов идентична структуре таблицы, с которой связан триггер. Они нужны для сравнения таблицы до и после изменения данных, чтобы принимать соответствующие меры в зависимости от наличия или отсутствия различий.

Как понять, что вашей базе данных необходимы триггеры? 

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

Механика создания триггера в языке SQL следующая: 

1. CREATE TRIGGER: создается триггер с указанием имени, таблицы или представления, для которых он предназначен, и типа триггера (AFTER или INSTEAD OF).
2. INSERT, UPDATE или DELETE: определяются операции, для которых предназначен триггер (INSERT, UPDATE или DELETE).
3. AS: начинается тело триггера с набором выражений SQL, которые отвечают за определенные действия при выполнении указанных операций.

По итогу при выполнении INSERT, UPDATE или DELETE триггер срабатывает автоматически, выполняя указанные выражения в SQL.

Структура создания триггера выглядит следующим образом:

CREATE TRIGGER имя_триггера
ON таблица/представление
AFTER/INSTEAD OF INSERT/UPDATE/DELETE
AS
BEGIN
  -- Тело триггера (выражения SQL)
END;

Рассмотрим каждый элемент подробнее:

• CREATE TRIGGER: ключевое слово, которое сообщает системе SQL о создании нового триггера.
• имя_триггера: уникальное имя, которое вы присваиваете триггеру.
• ON таблица/представление: указывает таблицу или представление, к которой привязан триггер.
• AFTER/INSTEAD OF: определяет момент срабатывания триггера:
  • AFTER: после выполнения операции INSERT, UPDATE или DELETE;
  • INSTEAD OF: вместо выполнения операции INSERT, UPDATE или DELETE.
• INSERT/UPDATE/DELETE: указывает операцию, при которой должен срабатывать триггер.
• AS: служебное слово, которое отделяет операторы от тела триггера.
• BEGIN: начало тела триггера.
• — Тело триггера (выражения SQL): здесь вы пишете код на SQL, который будет выполняться при срабатывании триггера.
• END;: завершение тела триггера.

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

CREATE TRIGGER before_order_insert
ON orders
BEFORE INSERT
AS
BEGIN
  -- Проверка наличия товара на складе
  IF (SELECT quantity FROM products WHERE id = NEW.product_id) <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Недостаточно товара на складе';
  END IF;
END;

В данном примере:

Триггер before_order_insert срабатывает перед добавлением новой записи в таблицу orders. Он проверяет количество товара на складе (таблица products) по идентификатору товара (product_id) из новой записи. Если товара недостаточно, триггер генерирует ошибку с сообщением «Недостаточно товара на складе».

Помните:

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

Виды событий, которые могут вызывать триггеры

Сами события, которые вызывают триггеры в SQL, делятся на два класса:

1. События внутри DDL (Data Definition Language). Это язык определения данных, который работает с изменением структуры таблицы (создание, модификация, удаление таблицы) или с событиями на уровне сервера (изменение безопасности, обновление статистики). Если внутри этого языка происходит изменение, триггер активируется. 
2. DML (Data Manipulation Language) Это язык для работы с данными в базе. Он включает операции для добавления, изменения и удаления данных. Если происходят какие-либо события, связанные с этим языком, запускается триггер. 

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

1. Тип FOR или AFTER: выполняются после завершения работы оператора, вызвавшего срабатывание триггера (INSERT, UPDATE, DELETE).
2. Тип INSTEAD OF: выполняются вместо оператора, вызвавшего срабатывание триггера, заменяя его действие.

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

1. Включить триггер: используйте команду ALTER TABLE table_name ENABLE TRIGGER trigger_name.
2. Отключить триггер: используйте команду ALTER TABLE table_name DISABLE TRIGGER trigger_name.
3. Изменить триггер: используйте команду ALTER TRIGGER trigger_name ON table_name RENAME TO new_trigger_name или ALTER TRIGGER trigger_name ON table_name DEPENDS ON EXTENSION extension_name.

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

Например, компания Oracle, которая специализируется на выпуске систем управления базами данных (ERP- и CRM-систем, отраслевых приложений), активно работает с триггерами и их функционалом. Это помогает компании соблюдать сложные бизнес-правила заказчика, которые не могут быть установлены с помощью ограничений целостности (UNIQUE, NOT NULL, CHECK). Кроме того, продукты клиентов всегда защищены от недействительных транзакций, а еще в процессе работы с базами данных от Oracle, где есть триггеры, можно собирать статистику и проводить аудит. Примерно для таких же целей используют триггеры во многих других компаниях: в PostgreSQL, MySQL, Postgres Pro Enterprise. 

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

Подводя итог

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

Изучив основы работы с триггерами в SQL, можно:

• сократить объем рутинного кода, сделать работу с базой данных более эффективной;
• обеспечить целостность и сохранность данных, автоматически выполняя проверки и обновления;
• реализовать сложные бизнес-правила без необходимости вносить изменения в основное приложение;
• расширить функциональность базы данных, добавляя новые возможности без изменения ее структуры.