介绍
在本实验中，你将学习如何使用 MySQL 触发器（triggers）来自动化数据库操作。触发器是存储程序，它们会在表上发生特定事件（如 INSERT、UPDATE 或 DELETE 操作）时自动执行。
你将创建一个 products 表来存储商品信息，以及一个 product_logs 表来审计变更。然后，你将实现一个触发器，该触发器会在每次向 products 表插入新商品时，自动向 product_logs 表添加一条日志记录。本实验还将指导你完成触发器的测试、更新和删除。
设置数据库和表
在创建触发器之前，你需要一个数据库和必要的表。在此步骤中，你将创建一个名为 mydatabase 的数据库和两个表：products 用于存储商品数据，product_logs 用于存储审计日志。
首先，从你的桌面打开终端。
以 root 用户连接到 MySQL 服务器。由于这是一个实验环境，你可以使用 sudo 来连接而无需密码。
sudo mysql -u root
连接成功后，你将看到 MySQL 提示符 (mysql>)。
现在，创建 mydatabase 数据库并切换到该数据库。IF NOT EXISTS 子句可以防止数据库已存在时出错。
CREATE DATABASE IF NOT EXISTS mydatabase;
USE mydatabase;
接下来，创建 products 表。该表将存储每个商品的 ID、名称和价格。
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: 指定触发器将在products表上发生INSERT事件后触发。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 shell。
exit;
总结
在本实验中，你学习了使用 MySQL 触发器进行自动化的基础知识。你成功设置了数据库环境，创建了表，并实现了一个触发器来记录数据插入事件。你还实践了触发器的完整生命周期，包括创建、测试、更新和删除。
这些技能对于创建审计跟踪、强制执行复杂的业务规则以及在数据库中自动维护数据完整性等任务至关重要。
