Автор выбрал фонд Apache Software Foundation для получения пожертвования в рамках программы Write for DOnations.
Триггер в MySQL — это определяемая пользователем SQL-команда, которая автоматически вызывается во время операций INSERT
, DELETE
или UPDATE
. Код триггера связан с таблицей и уничтожается после удаления таблицы. Вы можете определить время действия триггера и указать, когда его нужно активировать – до или после определенного события базы данных.
Триггеры имеют несколько преимуществ Например, вы можете использовать их для генерации значения производного столбца во время выполнения INSERT
. Еще один вариант использования триггера – обеспечение ссылочной целостности при сохранении записи в нескольких связанных таблицах. Также к преимуществам триггеров относятся регистрация действий пользователя для аудита таблиц и оперативное копирование данных в разных схемах баз данных для обеспечения избыточности и предотвращения единой точки отказа.
Вы также можете использовать триггеры, чтобы сохранить правила проверки на уровне базы данных. Это помогает избежать нарушения бизнес-логики при совместном использовании одного источника данных несколькими приложениями. Это значительно уменьшает количество обращений к серверу базы данных, что, в свою очередь, улучшает время отклика приложений. Поскольку сервер базы данных выполняет триггеры, они могут воспользоваться улучшенными ресурсами сервера, такими как RAM и CPU.
В этом обучающем модуле вы научитесь создавать, использовать и удалять различные типы триггеров в вашей базе данных MySQL.
Прежде чем начать, убедитесь в наличии следующего:
На этом этапе вы создадите тестовую клиентскую базу данных пользователя с несколькими таблицами для демонстрации работы триггеров MySQL.
Более подробно о работе MySQL можно прочитать в инструкции Запросы в MySQL.
Вначале войдите на сервер MySQL как root:
- mysql -u root -p
По запросу введите свой root пароль MySQL и нажмите ENTER
для продолжения. Когда вы увидите mysql>
, выполните следующую команду, чтобы создать базу данных test_db
:
- Create database test_db;
OutputQuery OK, 1 row affected (0.00 sec)
Далее переходите к test_db
с помощью:
- Use test_db;
OutputDatabase 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;
OutputQuery 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
вы увидите следующий вывод:
OutputQuery 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;
OutputQuery 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;
OutputQuery OK, 0 rows affected (0.02 sec)
Имея базу данных test_db
и четыре таблицы, теперь вы можете перейти к работе с различными триггерами MySQL в вашей базе данных.
На этом этапе вы изучите синтаксис триггера 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 одну за другой:
- 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');
OutputERROR 1644 (45000): Sale has exceeded the allowed amount of 10000.
Эта ошибка показывает, что код триггера работает должным образом.
Теперь попробуйте новую запись со значением 7500
, чтобы проверить правильность действия команды:
- Insert into sales(sales_id, customer_id, sales_amount) values('1','1','7500');
Поскольку значение находится в рекомендованном диапазоне, вы увидите следующий вывод:
OutputQuery 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
для сохранения связанной информации в разных таблицах.
Триггеры 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 ;
OutputQuery 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');
OutputQuery 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
.
Триггер 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
:
OutputERROR 1644 (45000): A VIP customer can not be downgraded.
Если вы выполните ту же команду для клиента уровня BASIC
и попытаетесь повысить учетную запись до уровня VIP
, команда выполнится успешно:
- Update customers set level='VIP' where customer_id='1';
OutputRows matched: 1 Changed: 1 Warnings: 0
Вы использовали триггер BEFORE UPDATE
для применения бизнес-правила. Теперь перейдем к использованию триггера 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');
OutputQuery OK, 1 row affected (0.00 sec)
Затем обновите запись:
- Update sales set sales_amount='9000' where sales_id='5';
Вывод должен выглядеть так:
OutputRows 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
для обеспечения целостности ссылок на уровне базы данных.
Триггеры 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';
В результате вы получите следующий вывод:
OutputERROR 1644 (45000): The customer has a related sales record.
Триггер BEFORE DELETE
может предотвратить случайное удаление связанной информации в базе данных.
В некоторых ситуациях может потребоваться удалить из разных связанных таблиц все записи, связанные с конкретной записью. В этой ситуации возможно использовать триггер 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';
OutputQuery OK, 1 row affected (0.00 sec)
Теперь проверьте, удалились ли записи для этого клиента из таблицы sales
:
- Select * from customers where customer_id='2';
Вы получите вывод Empty Set
, поскольку запись клиента, связанная с customer_id
2
, была удалена триггером:
OutputEmpty set (0.00 sec)
Вы научились использовать все виды триггеров для выполнения разных функций. Далее вы узнаете, как удалить триггер из базы данных, если он вам больше не нужен.
Как и любой другой объект базы данных, вы можете удалить триггеры с помощью команды DROP
. Синтакс удаления триггера следующий:
Drop trigger [TRIGGER NAME];
Например, чтобы удалить последний созданный триггер AFTER DELETE
, выполните следующую команду:
- Drop trigger delete_related_info;
OutputQuery OK, 0 rows affected (0.00 sec)
Необходимость удаления триггеров возникает, когда вы хотите воссоздать его структуру. В таком случае вы можете сбросить триггер и создать новый с помощью разных команд для триггеров.
В этом обучающем руководстве вы научились создавать, использовать и удалять различные триггеры из базы данных MySQL. На примере клиентской базы данных вы ознакомились с применением триггеров для различных целей, таких как проверка данных, применение бизнес-логики, ведение журнала аудита и обеспечение целостности ссылок.
Дополнительную информацию по использованию вашей базы данных MySQL можно найти здесь:
Join our DigitalOcean community of over a million developers for free! Get help and share knowledge in our Questions & Answers section, find tutorials and tools that will help you grow as a developer and scale your project or business, and subscribe to topics of interest.
Sign up