Что такое триггеры в sql

Триггеры

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

Формальное определение триггера:

CREATE TRIGGER имя_триггера ON  [INSERT | UPDATE | DELETE] AS выражения_sql

Для создания триггера применяется выражение CREATE TRIGGER , после которого идет имя триггера. Как правило, имя триггера отражает тип операций и имя таблицы, над которой производится операция.

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

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

• AFTER : выполняется после выполнения действия. Определяется только для таблиц.
• INSTEAD OF : выполняется вместо действия (то есть по сути действие — добавление, изменение или удаление — вообще не выполняется). Определяется для таблиц и представлений

После типа триггера идет указание операции, для которой определяется триггер: INSERT , UPDATE или DELETE .

Для триггера AFTER можно применять сразу для нескольких действий, например, UPDATE и INSERT. В этом случае операции указываются через запятую. Для триггера INSTEAD OF можно определить только одно действие.

И затем после слова AS идет набор выражений SQL, которые собственно и составляют тело триггера.

Создадим триггер. Допустим, у нас есть база данных productsdb со следующим определением:

CREATE DATABASE productdb; GO USE productdb; CREATE TABLE Products ( Id INT IDENTITY PRIMARY KEY, ProductName NVARCHAR(30) NOT NULL, Manufacturer NVARCHAR(20) NOT NULL, ProductCount INT DEFAULT 0, Price MONEY NOT NULL );

Определим триггер, который будет срабатывать при добавлении и обновлении данных:

USE productdb; GO CREATE TRIGGER Products_INSERT_UPDATE ON Products AFTER INSERT, UPDATE AS UPDATE Products SET Price = Price + Price * 0.38 WHERE Id FROM inserted)

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

Таким образом, триггер будет срабатывать при любой операции INSERT или UPDATE над таблицей Products. Сам триггер будет изменять цену товара, а для получения того товара, который был добавлен или изменен, находим этот товар по Id. Но какое значение должен иметь Id такой товар? Дело в том, что при добавлении или изменении данные сохраняются в промежуточную таблицу inserted. Она создается автоматически. И из нее мы можем получить данные о добавленных/измененных товарах.

И после добавления товара в таблицу Products в реальности товар будет иметь несколько большую цену, чем та, которая была определена при добавлении:

Удаление триггера

Для удаления триггера необходимо применить команду DROP TRIGGER :

DROP TRIGGER Products_INSERT_UPDATE

Отключение триггера

Бывает, что мы хотим приостановить действие триггера, но удалять его полностью не хотим. В этом случае его можно временно отключить с помощью команды DISABLE TRIGGER :

DISABLE TRIGGER Products_INSERT_UPDATE ON Products

А когда триггер понадобится, его можно включить с помощью команды ENABLE TRIGGER :

ENABLE TRIGGER Products_INSERT_UPDATE ON Products

SQL-Ex blog

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

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

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

• Пользователь не может выполнить триггеры вручную.
• Триггеры не принимают параметры.
• Вы не можете зафиксировать или откатить транзакцию внутри триггера.

Классы триггеров SQL Server

• Триггеры DDL (язык определения данных). Этот класс триггеров срабатывает на событиях, которые изменяют структуру (типа создания, модификации или удаления таблицы), или на определенных событиях, относящихся к серверу, например, событиях изменения безопасности или обновления статистики.
• Триггеры DML (язык модификации данных). Это наиболее часто используемый класс событий. Это события, которые вызывают срабатывание триггера при модификации данных; это может быть оператор insert, update или delete, применяемый к таблице или представлению.

• FOR или AFTER [INSERT, UPDATE, DELETE]: Эти типы триггеров выполняются после завершения выполнения оператора, вызвавшего срабатывание триггера (insert, update или delete).
• INSTEAD OF [INSERT, UPDATE, DELETE]: в отличие от типа FOR (AFTER) триггеры INSTEAD OF выполняются вместо оператора, вызвавшего срабатывание триггера. Другими словами, этот тип триггера заменяет вызвавший его оператор. Это очень полезно в случаях, когда вам нужно обеспечить ссылочную целостность между базами данных.

Почему триггеры важны?

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

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

Как узнать, какие строки были обновлены, вставлены или удалены, используя триггер DML в SQL Server?

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

Следует иметь в виду, что таблицы inserted и deleted не всегда доступны вместе (т.е. вы можете иметь таблицу inserted, но не иметь таблицы deleted, и наоборот). Вы можете найти больше информации об этих таблицах в следующей статье.

Синтаксис триггера DML в SQL Server

Вот базовый синтаксис команды создания триггера CREATE TRIGGER.

CREATE TRIGGER trigger_name
ON < Table name or view name >
[ WITH ]
< FOR | AFTER | INSTEAD OF >

В следующей таблице описывается каждый из аргументов синтаксиса CREATE TRIGGER.

Сценарии использования триггеров в SQL Server

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

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

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

Простой триггер SQL Server DML

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

Сначала мы должны создать таблицу Employees.

CREATE TABLE Employees 
( 
EmployeeID integer NOT NULL IDENTITY(1, 1) , 
EmployeeName VARCHAR(50) , 
EmployeeAddress VARCHAR(50) , 
MonthSalary NUMERIC(10, 2) 
PRIMARY KEY CLUSTERED (EmployeeID) 
) 
GO

Затем мы должны создать таблицу EmployeesAudit для хранения записей аудита. Эта таблица имеет ту же структуру, что и таблица Employees, плюс включает столбец AuditId в качестве первичного ключа, ModifiedDate для хранения даты модификации, ModifiedBy для того, чтобы мы могли узнать, кто модифицировал таблицу Employees, и, наконец, Operation, где будет указываться операция DML, которая сгенерировала запись аудита, одной из трех букв ( I для вставки, U для обновления и D для удаления).

CREATE TABLE EmployeesAudit 
( 
AuditID INTEGER NOT NULL IDENTITY(1, 1) , 
EmployeeID INTEGER , 
EmployeeName VARCHAR(50) , 
EmployeeAddress VARCHAR(50) , 
MonthSalary NUMERIC(10, 2) , 
ModifiedBy VARCHAR(128) , 
ModifiedDate DATETIME , 
Operation CHAR(1) 
PRIMARY KEY CLUSTERED ( AuditID ) 
) 
GO

Чтобы протестировать триггер, нам потребуется добавить некоторые данные в таблицу Employees.

INSERT INTO dbo.Employees 
( EmployeeName , 
EmployeeAddress , 
MonthSalary 
) 
SELECT 'Mark Smith', 'Ocean Dr 1234', 10000 
UNION ALL 
SELECT 'Joe Wright', 'Evergreen 1234', 10000 
UNION ALL 
SELECT 'John Doe', 'International Dr 1234', 10000 
UNION ALL 
SELECT 'Peter Rodriguez', '74 Street 1234', 10000 
GO

Теперь, когда мы имеем все для тестирования, пора создать наш триггер Посмотрите код ниже.

CREATE TRIGGER TR_Audit_Employees ON dbo.Employees 
FOR INSERT, UPDATE, DELETE 
AS 
DECLARE @login_name VARCHAR(128) 
SELECT @login_name = login_name 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID 
IF EXISTS ( SELECT 0 FROM Deleted ) 
BEGIN 
IF EXISTS ( SELECT 0 FROM Inserted ) 
BEGIN 
INSERT INTO dbo.EmployeesAudit 
( EmployeeID , 
EmployeeName , 
EmployeeAddress , 
MonthSalary , 
ModifiedBy , 
ModifiedDate , 
Operation 
) 
SELECT D.EmployeeID , 
D.EmployeeName , 
D.EmployeeAddress , 
D.MonthSalary , 
@login_name , 
GETDATE() , 
'U' 
FROM Deleted D 
END 
ELSE 
BEGIN 
INSERT INTO dbo.EmployeesAudit 
( EmployeeID , 
EmployeeName , 
EmployeeAddress , 
MonthSalary , 
ModifiedBy , 
ModifiedDate , 
Operation 
) 
SELECT D.EmployeeID , 
D.EmployeeName , 
D.EmployeeAddress , 
D.MonthSalary , 
@login_name , 
GETDATE() , 
'D' 
FROM Deleted D 
END 
END 
ELSE 
BEGIN 
INSERT INTO dbo.EmployeesAudit 
( EmployeeID , 
EmployeeName , 
EmployeeAddress , 
MonthSalary , 
ModifiedBy , 
ModifiedDate , 
Operation 
) 
SELECT I.EmployeeID , 
I.EmployeeName , 
I.EmployeeAddress , 
I.MonthSalary , 
@login_name , 
GETDATE() , 
'I' 
FROM Inserted I 
END 
GO

В начале код содержит получение пользователя, который модифицирует таблицу Employees, обращаясь к динамическому административному представлению sys.dm_exec_sessions для получения сессии по текущему
ИД сессии (SPID). Потом триггер вставляет одну запись в таблицу EmployeesAudit для каждой вставленной, обновленной или удаленной записи в таблице Employees, а так же текущее время и операцию DML, которая вызвала срабатывание триггера.

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

Первый из этих запросов выполняет обновление.

BEGIN TRANSACTION 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
UPDATE Employees 
SET EmployeeName = 'zzz' 
WHERE EmployeeID = 1 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
SELECT * 
FROM dbo.EmployeesAudit 
ROLLBACK TRANSACTION

На следующем скриншоте вы увидите обновленную запись в таблице Employees и новую запись таблице EmployeesAudit, которая отслеживает операцию DML в таблице Employees.

Второй запрос вставляет две строки в таблицу Employees.

BEGIN TRANSACTION 
INSERT INTO dbo.Employees 
( EmployeeName , 
EmployeeAddress , 
MonthSalary 
) 
SELECT 'zz' , 
'dsda' , 
10000 
UNION ALL 
SELECT 'Markus Rubius' , 
'dsda' , 
6000 
SELECT * 
FROM dbo.Employees 
SELECT * 
FROM dbo.EmployeesAudit 
ROLLBACK TRANSACTION

На скриншоте ниже вы можете увдеть две вставленные записи в таблице Employees и соответствующие записи аудита в таблице EmployeesAudit.

Наконец, третий запрос — оператор удаления из таблицы Employees.

BEGIN TRANSACTION 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
DELETE FROM dbo.Employees 
WHERE EmployeeID = 1 
SELECT * 
FROM dbo.EmployeesAudit 
SELECT * 
FROM dbo.Employees 
WHERE EmployeeID = 1 
ROLLBACK TRANSACTION

На скриншоте видно удаление строки из таблицы Employees и соответствующую запись аудита в таблице EmployeesAudit.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Триггеры в SQL

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

Общие сведения

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

Создание триггеров в SQL

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

CREATE TRIGGER name_trigger

• BEFORE INSERT
• BEFORE UPDATE
• BEFORE DELETE
• AFTER INSERT
• AFTER UPDATE
• AFTER DELETE

То есть триггер срабатывает либо до, дибо после вставки, обновления, удаления данных из БД в SQL.

Пример работы в SQL

Если вы не знакомы со структурой нашей БД, то советуем почитать предыдущие уроки.

Рассмотрим тестовую задачу, которая покажет возможности триггеров. Предположим, что в таблице orders нам нужно поменять цену (поле amt), а новое значение, которое мы введем, увеличить еще на 20%. Задача бывает полезна, когда нужно сделать наценку на товар.

Чтобы нам не высчитывать 20% вручную от новой цены — создадим триггер. Он автоматически будет увеличивать новую цену на 20%.
Вот код создания такого триггера:

DELIMITER // CREATE TRIGGER Before_Update_amt BEFORE UPDATE ON orders FOR EACH ROW BEGIN SET NEW.amt = NEW.amt * 1.2; END // DELIMITER ;

Заметьте, что название триггера (Before_Update_amt) лучше всего давать такое, чтобы было понятно при каком случае он срабатывает. Триггер срабатывает перед обновлением потому, что сначала мы должны узнать новое значение, а только потом его занести в поле.

Отметим также ключевого слово NEW — это то значение, которое должно было попасть в таблицу, но мы создали триггер и теперь это значение еще увеличивается на 20%.

Следующий момент — цикл FOR EACH ROW. Он необходим потому, что одновременно может изменяться не одно значение, а несколько строк. Вот, для каждой измененной строчки мы и увеличиваем значение на 20%.

Триггер на взаимодействие таблиц

Рассмотрим еще одну задачу: у нас есть продавец (в таблице salespeople), и его продажи отражены в таблицы orders. Представим теперь, что продавец увольняется и все его продажи тоже следует удалить. Если таких продаж много, то легче всего воспользоваться триггером.

DELIMITER // CREATE TRIGGER After_Delete_salespeople AFTER DELETE ON salespeople FOR EACH ROW BEGIN DELETE FROM orders WHERE orders.snum = OLD.snum; END // DELIMITER ;

Итак, после удаления продавца из salespeople берется его уникальный номер snum — он записан в коде как OLD.snum. Затем, по этому уникальному номеру удаляются все строчки из таблицы orders.

Можете проверить этот код, или его аналог. После удаления продавца триггер в SQL удаляет все записи из таблицы orders.

Ключевые слова OLD и NEW

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

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

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

Заключение

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

Поделиться ссылкой:

Использование триггеров базы данных MySQL в Ubuntu 18.04

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

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

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

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

Предварительные требования

Прежде чем начать, убедитесь в наличии следующего:

• Один сервер Ubuntu 18.04, настроенный в соответствии с инструкциями по начальной настройке сервера с Ubuntu 18.04, а также пользователь sudo без прав root.
• База данных MySQL, работающая на вашем сервере в соответствии с инструкцией по установке MySQL на Ubuntu 18.04
• Учетные данные root пользователя для вашей базы данных MySQL.

Шаг 1 — Создание тестовой базы данных

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

Более подробно о работе MySQL можно прочитать в инструкции Запросы в MySQL​​​.

Вначале войдите на сервер MySQL как root:

По запросу введите свой root пароль MySQL и нажмите ENTER для продолжения. Когда вы увидите mysql> , выполните следующую команду, чтобы создать базу данных test_db :

Output
Query OK, 1 row affected (0.00 sec) 

Далее переходите к test_db с помощью:

Output
Database changed 

Начинайте с создания таблицы customers . В этой таблице будут храниться записи клиентов, включая customer_id , customer_name и level . Будет два типа клиентов: BASIC и VIP .

Output
Query OK, 0 rows affected (0.01 sec) 

Теперь, добавьте несколько записей в таблицу customers . Для этого выполните следующие команды одну за другой:

После выполнения каждой команды INSERT вы увидите следующий вывод:

Output
Query OK, 1 row affected (0.01 sec) 

Чтобы убедиться, что тестовые записи были успешно вставлены, выполните команду SELECT :

Output
+-------------+---------------+-------+ | customer_id | customer_name | level | +-------------+---------------+-------+ | 1 | JOHN DOE | BASIC | | 2 | MARY ROE | BASIC | | 3 | JOHN DOE | VIP | +-------------+---------------+-------+ 3 rows in set (0.00 sec) 

Затем создайте другую таблицу customers для хранения соответствующей информации об учетной записи клиентов. Таблица будет содержать поля customer_id и status_notes .

Запустите следующую команду:

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

Output
Query OK, 0 rows affected (0.01 sec) 

Вы сможете добавить тестовые данные в колонку sales на следующих этапах во время тестирования триггеров. Далее создайте таблицу audit_log для регистрации обновлений, внесенных в таблицу sales при имплементации триггера AFTER UPDATE в шаге 5:

«>Create table audit_log(log_id BIGINT PRIMARY KEY AUTO_INCREMENT, sales_id BIGINT, previous_amount DOUBLE, new_amount DOUBLE, updated_by VARCHAR(50), updated_on DATETIME )ENGINE=INNODB;

Output
Query OK, 0 rows affected (0.02 sec) 

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

Шаг 2 — Создание триггера Before Insert

На этом этапе вы изучите синтаксис триггера MySQL перед тем, как применить эту логику для создания триггера BEFORE INSERT , который проверяет поле sales_amount перед вставкой данных в таблицу sales .

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

DELIMITER // CREATE TRIGGER [TRIGGER_NAME] [TRIGGER TIME] [TRIGGER EVENT] ON [TABLE] FOR EACH ROW [TRIGGER BODY]// DELIMITER ; 

Структура триггера включает:

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

[TRIGGER_NAME] : триггер должен иметь имя, и вы можете указать его именно здесь.

[TRIGGER TIME]​​​ : триггер может быть вызван в разные моменты времени. MySQL позволяет определить, когда запускать триггер — до или после операции с базой данных.

[TRIGGER EVENT] : триггеры могут быть вызваны только операциями INSERT , UPDATE и DELETE . Вы можете использовать любое из значений в зависимости от того, чего вы хотите достичь.

[TABLE] : любой триггер, который вы создаете в своей базе данных MySQL, должен быть связан с таблицей.

FOR EACH ROW : этот оператор позволяет MySQL выполнять код триггера для каждой строки, на которую влияет триггер.

[TRIGGER BODY] : код, который выполняется при вызове триггера, называется trigger body​​. Это может быть один SQL-оператор или несколько команд. Обратите внимание, если вы выполняете несколько SQL-операторов в теле триггера, вы должны заключить их в блок BEGIN. END .

Примечание: при создании тела триггера вы можете использовать ключевые слова OLD и NEW для доступа к старым и новым значениям колонки, введенным во время операции INSERT , UPDATE и DELETE . В триггере DELETE может быть использовано только ключевое слово OLD (подробнее об этом в шаге 4).

Теперь вы можете создать свой первый триггер BEFORE INSERT . Триггер будет связан с таблицей sales и будет вызываться перед вставкой записи для проверки sales_amount . Функция триггера состоит в том, чтобы проверить, превышает ли значение sales_amount , вставляемое в таблицу продаж, величину 10000 , и выдать ошибку, если это так.

Убедитесь, что вы вошли на сервер MySQL. Затем введите следующие команды MySQL одну за другой:

Используйте IF. THEN. END IF​​​ для оценки того, находится ли сумма, указанная в операторе INSERT , в пределах вашего диапазона. Триггер может извлечь новое значение sales_amount , используя ключевое слово NEW .

Чтобы вызвать общее сообщение об ошибке, используются следующие строки для информирования пользователя:

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.'; 

Далее вставьте запись sales_amount со значением 11000 в таблицу sales , чтобы проверить, остановит ли триггер операцию:

Output
ERROR 1644 (45000): Sale has exceeded the allowed amount of 10000. 

Эта ошибка показывает, что код триггера работает должным образом.

Теперь попробуйте новую запись со значением 7500 , чтобы проверить правильность действия команды:

Поскольку значение находится в рекомендованном диапазоне, вы увидите следующий вывод:

Output
Query OK, 1 row affected (0.01 sec) 

Для подтверждения вставки данных запустите следующую команду:

Вывод подтверждает вставку данных в таблицу:

Output
+----------+-------------+--------------+ | sales_id | customer_id | sales_amount | +----------+-------------+--------------+ | 1 | 1 | 7500 | +----------+-------------+--------------+ 1 row in set (0.00 sec) 

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

Теперь поработайте с триггером AFTER INSERT для сохранения связанной информации в разных таблицах.

Шаг 3 — Создание триггера After Insert

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

На этом этапе вы поработаете с таблицей customer_status​​​ , используя триггер AFTER INSERT для ввода связанных клиентских записей.

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

Output
Query OK, 0 rows affected (0.00 sec) 

Таким образом вы инструктируете MySQL сохранить еще одну запись в таблицу customer_status , как только происходит вставка новой клиентской записи в таблицу customers .

Теперь вставьте новую запись в таблицу customers , чтобы убедиться, что код триггера вызывается:

Output
Query OK, 1 row affected (0.01 sec) 

После успешной вставки записи убедитесь, что запись нового статуса была добавлена в таблицу customer_status :

Output
+-------------+-----------------------------+ | customer_id | status_notes | +-------------+-----------------------------+ | 4 | ACCOUNT OPENED SUCCESSFULLY | +-------------+-----------------------------+ 1 row in set (0.00 sec) 

Вывод подтверждает успешную работу триггера.

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

На следующем этапе вы будете работать с триггерами UPDATE .

Шаг 4 — Создание триггера Before Update

Триггер BEFORE UPDATE схож с триггером BEFORE INSERT , разница заключается в том, когда они вызываются. Вы можете использовать триггер BEFORE UPDATE для проверки бизнес-логики перед обновлением записи. Для проверки используйте таблицу customers , в которую вы уже вставили некоторые данные.

В базе данных есть два типа клиентов. В этом примере после того, как учетная запись клиента будет обновлена до уровня VIP , она не сможет быть понижена до уровня BASIC . Чтобы применить такое правило, создайте триггер BEFORE UPDATE , который будет выполняться перед оператором UPDATE , как показано ниже. Если пользователь базы данных попытается понизить клиента до уровня BASIC с уровня VIP , будет активировано определяемое пользователем исключение.

Введите следующие команды SQL одну за другой, чтобы создать триггер BEFORE UPDATE :

Используйте ключевое слово OLD для фиксации уровня, предоставленного пользователем при выполнении команды UPDATE . Опять же, вы используете IF. THEN. END IF , чтобы сообщить пользователю об общей ошибке.

Далее выполните следующую SQL команду, которая попытается понизить учетную запись клиента, имеющую идентификатор customer_id , равный 3 :

Вы увидите следующий вывод, предоставляющий SET MESSAGE_TEXT :

Output
ERROR 1644 (45000): A VIP customer can not be downgraded. 

Если вы выполните ту же команду для клиента уровня BASIC и попытаетесь повысить учетную запись до уровня VIP , команда выполнится успешно:

Output
Rows matched: 1 Changed: 1 Warnings: 0 

Вы использовали триггер BEFORE UPDATE для применения бизнес-правила. Теперь перейдем к использованию триггера AFTER UPDATE для ведения журнала аудита.

Шаг 5 — Создание триггера After Update

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

Вы создаете триггер, который регистрирует активность обновления таблицы sales . Наша таблица audit_log будет содержать информацию о пользователях MySQL, обновляющих таблицу sales , дату обновления date , а также новые new и старые old значения sales_amount .

Для создания триггера, выполните следующие команды SQL:

Вы вставляете новую запись в таблицу audit_log . Вы используете ключевое слово NEW для получения значения sales_id и нового значения sales_amount . Также вы используете ключевое слово OLD для получения предыдущего значения sales_amount , если вы хотите зарегистрировать обе суммы для аудита.

Команда SELECT USER() извлекает текущего пользователя, выполняющего операцию, а оператор NOW() извлекает значение текущей даты и времени с сервера MySQL.

Теперь, если пользователь попытается обновить значение какой-либо записи в таблице sales , триггер log_sales_updates вставит новую запись в таблицу audit_log .

Давайте создадим новую запись о продажах со случайным значением sales_id , равным 5 , и попробуем обновить ее. Сначала вставьте запись о продажах:

Output
Query OK, 1 row affected (0.00 sec) 

Затем обновите запись:

Вывод должен выглядеть так:

Output
Rows matched: 1 Changed: 1 Warnings: 0 

Теперь выполните следующую команду, чтобы проверить, смог ли триггер AFTER UPDATE зарегистрировать новую запись в таблице audit_log :

Триггер зарегистрировал обновление. Ваш вывод должен показать предыдущую сумму sales_amount и новую сумму new amount​​ , зарегистрированную пользователем, который обновил запись:

Output
+--------+----------+-----------------+------------+----------------+---------------------+ | log_id | sales_id | previous_amount | new_amount | updated_by | updated_on | +--------+----------+-----------------+------------+----------------+---------------------+ | 1 | 5 | 8000 | 9000 | root@localhost | 2019-11-07 09:28:36 | +--------+----------+-----------------+------------+----------------+---------------------+ 1 row in set (0.00 sec) 

Также в таблице вы увидите дату и время, когда было выполнено обновление, что важно для аудита.

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

Шаг 2 — Создание триггера Before Delete

Триггеры BEFORE DELETE вызываются до выполнения операции DELETE в таблице. Этот вид триггеров обычно используется для обеспечения целостности ссылок в разных связанных таблицах. Например, каждая запись в таблице sales связана с записью customer_id из таблицы customers . Если пользователь базы данных удалил из таблицы customers запись, у которой есть связанная запись в таблице sales , у вас не будет возможности узнать, какой клиент был связан с этой записью.

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

Теперь попробуйте удалить клиента, у которого есть связанная запись в таблице sales:

В результате вы получите следующий вывод:

Output
ERROR 1644 (45000): The customer has a related sales record. 

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

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

Шаг 5 — Создание триггера After Delete

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

Еще один вариант использования триггера AFTER DELETE — удаление связанной информации из других таблиц после удаления записи из базовой таблицы. Например, вы можете установить триггер, который удаляет запись о клиенте, если записи о продажах с соответствующим customer_id будут удалены из таблицы sales . Запустите следующую команду для создания триггера:

Далее запустите следующую команду, чтобы удалить все записи о продажах, связанных с customer_id , равному 2 :

Output
Query OK, 1 row affected (0.00 sec) 

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

Вы получите вывод Empty Set , поскольку запись клиента, связанная с customer_id 2 , была удалена триггером:

Output
Empty set (0.00 sec) 

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

Шаг 8 — Удаление триггеров

Как и любой другой объект базы данных, вы можете удалить триггеры с помощью команды DROP . Синтакс удаления триггера следующий:

Drop trigger [TRIGGER NAME]; 

Например, чтобы удалить последний созданный триггер AFTER DELETE , выполните следующую команду:

Output
Query OK, 0 rows affected (0.00 sec) 

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

Заключение

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

Дополнительную информацию по использованию вашей базы данных MySQL можно найти здесь:

• Оптимизация работы MySQL с помощью кеша запросов в Ubuntu 18.04
• Применение постраничного ввода данных на MySQL с PHP в Ubuntu 18.04​​​
• Устранение ошибок на MySQL

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.