Баннер мобильный (1) Пройти тест

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

Разбираем основы на примере SQL

Разбор

30 мая 2024

Поделиться

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

Содержание

    В мире баз данных триггеры играют роль незаменимых помощников, автоматизируя рутинные задачи и расширяя функциональность системы. Представьте себе механизм, который срабатывает в ответ на определенные события, инициируя цепочку действий, — именно так можно описать работу триггера. Рассказываем, что такое триггеры в 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, можно:

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

    Разбор

    Поделиться

    Скопировано
    0 комментариев
    Комментарии