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

3 марта, 2020 12:36 пп 1 400 views | Комментариев нет

mySQL, Ubuntu | Amber | Комментировать запись

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

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

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

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

Требования

Сервер Ubuntu 18.04, настроенный согласно этому мануалу.
Сервер MySQL, настроить который вам поможет этот мануал.
Учетные данные пользователя root MySQL.

1: Создание базы данных

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

Читайте также: Запросы в MySQL

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

mysql -u root -p

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

Create database test_db; Query OK, 1 row affected (0.00 sec)

Затем перейдите в test_db с помощью этой команды:

Use test_db; 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; Query OK, 0 rows affected (0.01 sec)

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

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

Query OK, 1 row affected (0.01 sec)

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

Затем мы создадим еще одну таблицу, customer_status, для хранения соответствующей информации об учетной записи клиентов. Таблица будет иметь поля 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; 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; 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]: код, который выполняется при вызове триггера, называется телом триггера. Это может быть один SQL оператор или несколько команд. Обратите внимание, если в теле триггера вы выполняете несколько SQL операторов, вы должны заключить их в блок BEGIN … END.

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

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

Убедитесь, что вы вошли на сервер 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'); 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');

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

Query OK, 1 row affected (0.01 sec)

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

Select * from sales;

Вывод подтверждает, что данные находятся в таблице:

+----------+-------------+--------------+ | 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 ; 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'); Query OK, 1 row affected (0.01 sec)

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

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

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

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:

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

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

Update customers set level='VIP' where customer_id='1'; Rows matched: 1 Changed: 1 Warnings: 0

Теперь давайте посмотрим, как работает триггер AFTER UPDATE для ведения журнала аудита.

5: Создание триггера AFTER UPDATE

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

Давайте создадим триггер, который регистрирует активность обновления таблицы sales. Таблица audit_log будет содержать информацию о пользователях MySQL, обновляющих таблицу sales, дату обновления, а также новые и старые значения 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'); Query OK, 1 row affected (0.00 sec)

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

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

Вы получите такой вывод:

Rows matched: 1 Changed: 1 Warnings: 0

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

Select * from audit_log;

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

+--------+----------+-----------------+------------+----------------+---------------------+ | 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 для обеспечения целостности ссылок на уровне базы данных.

6: Создание триггера BEFORE DELETE

Триггеры BEFORE DELETE вызываются до выполнения операции DELETE. Этот вид триггеров обычно используется для обеспечения ссылочной целостности в разных связанных таблицах. Например, каждая запись в таблице sales связана с записью в таблице customers через столбец customer_id. Если пользователь базы данных удалил из таблицы 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';

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

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

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

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

7: Создание триггера 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'; Query OK, 1 row affected (0.00 sec)

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

Select * from customers where customer_id='2';

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

Empty set (0.00 sec)

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

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

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

Drop trigger [TRIGGER NAME];

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

Drop trigger delete_related_info; Query OK, 0 rows affected (0.00 sec)

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

Заключение

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

Читайте также:

Tags: MySQL, Ubuntu 18.04

Добавить комментарий Отменить ответ

Для отправки комментария вам необходимо авторизоваться.