Триггеры SQL: основное руководство

Опубликовано: 2022-10-12

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

Это важное руководство предоставит вам подробную информацию о триггерах SQL, которая может оказаться весьма полезной в вашей профессии. Давайте начнем!

Что такое триггеры SQL?

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

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

Что такое SQL-триггеры

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

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

Синтаксис:

 CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];

Объяснение каждого параметра

  • CREATE TRIGGER trigger_name — используется для создания триггера или для изменения имени существующего триггера.
  • ДО/ПОСЛЕ — этот запрос используется для определения времени выполнения триггера (до или после определенного события).
  • INSERT/UPDATE/DELETE — описывает действие, которое мы хотим выполнить над таблицами.
  • ON tableName — здесь мы определяем имя таблицы для настройки триггера.
  • ДЛЯ КАЖДОЙ СТРОКИ — этот оператор относится к триггеру строки, что означает, что триггеры будут выполняться всякий раз, когда строка изменяется.
  • trigger_body — указывает действие, которое должно быть выполнено при срабатывании триггера.

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

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

Ниже приведены некоторые преимущества использования триггеров в операциях базы данных SQL.

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

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

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

Комбинация триггерных аргументов

Комбинация триггерных аргументов

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

BEFORE INSERT : эти триггеры выполняют действие над строками перед выполнением любых операций INSERT в указанной таблице или в базе данных.

AFTER INSERT : он выполняет действие над строками сразу после любого действия INSERT базы данных.

ПЕРЕД ОБНОВЛЕНИЕМ : с помощью этих триггеров функция над строками выполняется до выполнения действия ОБНОВЛЕНИЕ в базе данных.

ПОСЛЕ ОБНОВЛЕНИЯ : он выполняет действие над строками сразу после любого действия ОБНОВЛЕНИЯ базы данных или конкретной таблицы.

BEFORE DELETE : он выполняет определенную операцию над строками еще до того, как база данных или таблица будут подвергнуты действию DELETE.

AFTER DELETE : эти триггеры выполняют действие над строками после каждой транзакции DELETE.

Типы триггеров SQL

Типы SQL-триггеров

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

Триггеры DML — DML расшифровывается как язык манипулирования данными. Выполнение кода в ответ на изменение данных стало возможным с помощью триггеров DML. Этот триггер активируется при выполнении команд DML, таких как INSERT, UPDATE и DELETE. Их также называют «триггерами уровня таблицы».

Триггеры DDL — DDL означает язык определения данных. Триггеры DDL позволяют нам запускать код в ответ на изменения схемы базы данных, такие как добавление или удаление таблиц, или события сервера, такие как регистрация пользователя. Они называются «триггерами уровня базы данных».

Эти триггеры можно активировать, когда определенные операторы DDL, такие как CREATE, ALTER или DROP, выполняются в активной базе данных. Их также можно использовать для наблюдения и управления выполняемыми действиями.

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

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

Триггеры CLR — CLR расшифровывается как Common Language Runtime. Триггеры CLR действительно представляют собой уникальное подмножество триггеров, построенных в основном на CLR в рамках технологии .NET. Эти триггеры полезны, если триггеру необходимо выполнить множество вычислений или он должен быть связан с сущностью, отличной от SQL.

Триггеры DML и DDL действительно можно создать, включив кодирование поддерживаемых триггеров CLR в технологиях .NET, включая Visual Basic, C# и F-sharp.

Пример триггера SQL Server

Sample-SQL-Server-Trigger

Давайте разберемся с этими концепциями триггеров на примере.

Во-первых, давайте создадим базу данных с помощью операторов SQL.

 CREATE DATABASE testdb; use testdb;

Здесь я дал «testdb» в качестве имени базы данных. И следующим шагом будет создание таблицы.

 CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );

Я создал таблицу для хранения данных о студентах. А вот и команда для описания структуры таблицы. Здесь «студент» — это имя таблицы, которое я дал.

 DESC student;

Ниже представлена ​​структура таблицы, которую я создал.

 +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)

После создания таблицы следующим шагом будет настройка триггера. Давайте попробуем использовать аргумент BEFORE INSERT .

Название созданного мной триггера — «метки». Как только в таблицу вносятся оценки учащегося, нижеприведенный триггер пытается автоматически определить общую оценку учащегося.

 CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;

Поскольку нам требуется заменить данные строк, а не работать со старыми, мы определили «итого» с помощью нового имени класса, а все последующие выражения имеют префикс с новыми ключевыми словами после итога с помощью оператора точки. Теперь мы добавим значения в каждую строку и посмотрим на результаты. Изначально общая оценка равна 0 для каждого ученика.

 INSERT INTO student VALUES("George",02,99,87,92,91,0); INSERT INTO student VALUES("James",03,91,81,94,90,0); INSERT INTO student VALUES("Harry",04,86,70,73,88,0); INSERT INTO student VALUES("John",05,73,89,78,92,0); INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);

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

 SELECT * FROM table_name;

И вот окончательный вывод.

 mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)

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

Дополнительные триггерные операции

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

Запрос, чтобы проверить, существует ли конкретный триггер или нет

Эта команда проверяет указанный триггер во всей базе данных.

 SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'

Запрос для отображения триггеров

Все триггеры, доступные в активной базе данных, будут показаны следующим оператором.

 SHOW TRIGGERS;

Запрос на отключение триггера

Приведенная ниже команда деактивирует триггер в рабочей базе данных.

 DISABLE TRIGGER trigger_name ON DATABASE;

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

 DISABLE TRIGGER trigger_name ON table_name;

Запрос для включения триггера

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

 ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name

Триггер должен быть отключен, прежде чем пытаться его включить,

Запрос для включения или отключения всех триггеров в таблице

Используя приведенный выше оператор SQL, мы можем деактивировать или активировать все триггеры таблицы одновременно, подставив «ALL» вместо определенного имени триггера.

 ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL

Запрос на удаление или удаление триггера

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

 DROP TRIGGER [trigger_name];

Всякий раз, когда триггер удаляется, связанные с ним данные удаляются из таблицы данных sys.objects.

Преимущества триггеров

  • Создать триггеры просто, и сам триггер может вызывать сохраненные функции и методы.
  • Пользователи могут реализовать простой аудит с помощью триггеров.
  • К сожалению, вы не можете создавать ограничения для сущностей в системах баз данных с помощью SQL Server, хотя вы можете эмулировать действие ограничений, используя триггеры.
  • Ограничения целостности могут быть реализованы в базах данных с помощью триггеров.
  • Когда требуется групповая проверка, а не построчная проверка вновь введенных или измененных данных, могут быть полезны триггеры.

Недостатки триггеров

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

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

Вывод

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

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

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

Я надеюсь, что вы нашли эту статью полезной для изучения триггеров SQL.

Если вы хотите подробно изучить базы данных, вот несколько отличных ресурсов для изучения SQL и NoSQL.