MySQL Triggers для автоматизации

Введение

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

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

Настройка базы данных и таблиц

Прежде чем создавать триггер, вам потребуется база данных и необходимые таблицы. На этом шаге вы создадите базу данных с именем mydatabase и две таблицы: products для хранения данных о продуктах и product_logs для хранения журналов аудита.

Сначала откройте терминал на вашем рабочем столе.

Подключитесь к серверу MySQL от имени пользователя root. Поскольку это лабораторная среда, вы можете использовать sudo для подключения без пароля.

sudo mysql -u root

После подключения вы увидите приглашение MySQL (mysql>).

Теперь создайте базу данных mydatabase и переключитесь на нее. Предложение IF NOT EXISTS предотвращает ошибку, если база данных уже существует.

CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;

Далее создайте таблицу products. Эта таблица будет хранить идентификатор, название и цену каждого продукта.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

Теперь создайте таблицу product_logs. Эта таблица будет хранить журнал каждого добавленного нового продукта.

CREATE TABLE product_logs (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    action_description VARCHAR(255),
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Вы можете проверить, что обе таблицы были успешно созданы, выполнив:

SHOW TABLES;

В выводе вы должны увидеть таблицы products и product_logs.

+--------------------+
| Tables_in_mydatabase |
+--------------------+
| product_logs       |
| products           |
+--------------------+
2 rows in set (0.00 sec)

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

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

После создания таблиц вы можете создать триггер. Этот триггер будет активироваться после вставки новой строки в таблицу products и будет добавлять соответствующую запись в таблицу product_logs.

Переключитесь на вашу базу данных.

USE mydatabase;

Теперь создайте триггер. Команда DELIMITER используется для изменения стандартного разделителя (;) на //, чтобы точка с запятой внутри тела триггера не интерпретировалась как конец оператора CREATE TRIGGER.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name));
END//

DELIMITER ;

Разберем этот оператор:

• CREATE TRIGGER products_after_insert: Определяет новый триггер с именем products_after_insert.
• AFTER INSERT ON products: Указывает, что триггер будет срабатывать после события INSERT в таблице products.
• FOR EACH ROW: Указывает, что логика триггера будет выполняться для каждой вставляемой строки.
• BEGIN...END: Ограничивает SQL-операторы триггера.
• NEW.id, NEW.name: Ключевое слово NEW ссылается на только что вставленную строку. NEW.id — это id нового продукта, а NEW.name — его название.

Теперь протестируем триггер, вставив новый продукт.

INSERT INTO products (name, price) VALUES ('Laptop', 1200.00);

Эта операция INSERT должна была активировать триггер. Чтобы подтвердить это, проверьте содержимое таблицы product_logs.

SELECT * FROM product_logs;

Вы должны увидеть одну запись в таблице product_logs, которая была автоматически создана триггером. Вывод будет похож на этот ( log_id и log_time будут отличаться):

+--------+------------+-----------------------------+---------------------+
| log_id | product_id | action_description          | log_time            |
+--------+------------+-----------------------------+---------------------+
|      1 |          1 | New product added: Laptop   | 2024-05-27 10:30:00 |
+--------+------------+-----------------------------+---------------------+
1 row in set (0.00 sec)

Это подтверждает, что ваш триггер работает правильно.

Обновление логики триггера

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

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

Переключитесь на вашу базу данных.

USE mydatabase;

Сначала удалите существующий триггер. Предложение IF EXISTS предотвращает ошибку, если триггер уже был удален.

DROP TRIGGER IF EXISTS products_after_insert;

Теперь воссоздайте триггер с обновленной логикой. Эта новая версия будет включать цену продукта в action_description.

DELIMITER //

CREATE TRIGGER products_after_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_logs (product_id, action_description)
    VALUES (NEW.id, CONCAT('New product added: ', NEW.name, ' with price ', NEW.price));
END//

DELIMITER ;

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

INSERT INTO products (name, price) VALUES ('Mouse', 25.00);

Теперь выполните запрос к таблице product_logs, чтобы увидеть все записи журнала.

SELECT * FROM product_logs;

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

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

Вы успешно обновили триггер.

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

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

Переключитесь на вашу базу данных.

USE mydatabase;

Чтобы удалить триггер, используйте оператор DROP TRIGGER.

DROP TRIGGER IF EXISTS products_after_insert;

Вы можете убедиться, что триггер был удален, выполнив команду SHOW TRIGGERS.

SHOW TRIGGERS;

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

Empty set (0.00 sec)

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

INSERT INTO products (name, price) VALUES ('Keyboard', 75.00);

Теперь проверьте таблицу product_logs.

SELECT * FROM product_logs;

Таблица по-прежнему должна содержать только первые две записи журнала. Продукт 'Keyboard' не должен иметь записи в журнале, поскольку триггер был удален до вставки.

+--------+------------+----------------------------------------------+---------------------+
| log_id | product_id | action_description                           | log_time            |
+--------+------------+----------------------------------------------+---------------------+
|      1 |          1 | New product added: Laptop                    | 2024-05-27 10:30:00 |
|      2 |          2 | New product added: Mouse with price 25.00    | 2024-05-27 10:35:00 |
+--------+------------+----------------------------------------------+---------------------+
2 rows in set (0.00 sec)

Это подтверждает, что триггер был успешно удален. Теперь вы можете выйти из оболочки MySQL.

exit;

Резюме

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

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