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

Автор выбрал фонд Apache Software Foundation для получения пожертвования в рамках программы Write for DOnations.

Введение

Триггер в 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:

mysql -u root -p

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

Create database test_db;

Output
Query OK, 1 row affected (0.00 sec)

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

Use test_db;

Output
Database changed

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

Create table customers(customer_id BIGINT PRIMARY KEY, customer_name VARCHAR(50), level VARCHAR(50) ) ENGINE=INNODB;

Output
Query OK, 0 rows affected (0.01 sec)

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

Insert into customers (customer_id, customer_name, level )values('1','JOHN DOE','BASIC');
Insert into customers (customer_id, customer_name, level )values('2','MARY ROE','BASIC');
Insert into customers (customer_id, customer_name, level )values('3','JOHN DOE','VIP');

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

Output
Query OK, 1 row affected (0.01 sec)

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

Select * from customers;

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.

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

Create table customer_status(customer_id BIGINT PRIMARY KEY, status_notes VARCHAR(50)) ENGINE=INNODB;

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

Create table sales(sales_id BIGINT PRIMARY KEY, customer_id BIGINT, sales_amount DOUBLE ) ENGINE=INNODB;

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.

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

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

DELIMITER //
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
IF NEW.sales_amount>10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Sale has exceeded the allowed amount of 10000.';
END IF//
DELIMITER ;

Используйте 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, чтобы проверить, остановит ли триггер операцию:

Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');

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

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

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

Insert into  sales(sales_id, customer_id, sales_amount) values('1','1','7500');

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

Output
Query OK, 1 row affected (0.01 sec)

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

Select * from sales;

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

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 введите следующие команды:

DELIMITER //
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, 'ACCOUNT OPENED SUCCESSFULLY')//
DELIMITER ;

Output
Query OK, 0 rows affected (0.00 sec)

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

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

Insert into customers (customer_id, customer_name, level )values('4','DAVID DOE','VIP');

Output
Query OK, 1 row affected (0.01 sec)

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

Select * from 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:

DELIMITER //
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'A VIP customer can not be downgraded.';
END IF //
DELIMITER ;

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

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

Update customers set level='BASIC' where customer_id='3';

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

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

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

Update customers set level='VIP' where customer_id='1';

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:

DELIMITER //
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() )//
DELIMITER ;

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

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

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

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

Insert into sales(sales_id, customer_id, sales_amount) values('5', '2','8000');

Output
Query OK, 1 row affected (0.00 sec)

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

Update sales set sales_amount='9000' where sales_id='5';

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

Output
Rows matched: 1  Changed: 1  Warnings: 0

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

Select * from 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 команды одну за другой:

DELIMITER //
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'The customer has a related sales record.';
END IF//
DELIMITER ;

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

Delete from customers where customer_id='2';

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

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. Запустите следующую команду для создания триггера:

DELIMITER //
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
Delete from customers where customer_id=OLD.customer_id;//
DELIMITER ;

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

Delete from sales where customer_id='2';

Output
Query OK, 1 row affected (0.00 sec)

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

Select * from customers where customer_id='2';

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

Output
Empty set (0.00 sec)

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

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

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

Drop trigger [TRIGGER NAME];

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

Drop trigger delete_related_info;

Output
Query OK, 0 rows affected (0.00 sec)

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

Заключение

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

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

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