MySQL—触发器详解
基本介绍
触发器是与表有关的数据库对象，在 INSERT、UPDATE、DELETE 操作之前或之后触发并执行触发器中定义的 SQL 语句。
触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。
使用别名 NEW 和 OLD 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。
现在触发器还只支持行级触发，不支持语句级触发。
| 触发器类型 | OLD的含义 | NEW的含义 |
|---|---|---|
| INSERT 型触发器 | 无 (因为插入前状态无数据) | NEW 表示将要或者已经新增的数据 |
| UPDATE 型触发器 | OLD 表示修改之前的数据 | NEW 表示将要或已经修改后的数据 |
| DELETE 型触发器 | OLD 表示将要或者已经删除的数据 | 无 (因为删除后状态无数据) |
基本操作
创建触发器
DELIMITER $CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|UPDATE|DELETE
ON 表名
FOR EACH ROW -- 行级触发器
BEGIN触发器要执行的功能;
END$DELIMITER ;查看触发器的状态、语法等信息
SHOW TRIGGERS;删除触发器，如果没有指定 schema_name，默认为当前数据库
DROP TRIGGER [schema_name.]trigger_name;注意事项
- 确保触发器中的逻辑不影响性能，尤其是在高频操作的表上。
- 避免在触发器中出现无限循环的情况，例如更新触发器又触发了同一操作。
代码示例
通过触发器记录账户表的数据变更日志。包含：增加、修改、删除。
数据准备
CREATE TABLE accounts
(id INT AUTO_INCREMENT PRIMARY KEY, -- 账户IDusername VARCHAR(50) NOT NULL UNIQUE, -- 用户名，必须唯一password VARCHAR(255) NOT NULL, -- 密码，建议加密存储email VARCHAR(100) NOT NULL UNIQUE, -- 邮箱，必须唯一created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 创建时间，默认当前时间updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间，自动更新
);
CREATE TABLE account_change_log
(log_id INT AUTO_INCREMENT PRIMARY KEY, -- 日志IDaccount_id INT, -- 被更改的账户IDoperation_type VARCHAR(10), -- 操作类型：INSERT, UPDATE, DELETEold_value VARCHAR(255), -- 更新前的值new_value VARCHAR(255), -- 更新后的值changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 变更时间，默认当前时间FOREIGN KEY (account_id) REFERENCES accounts (id) -- 外键约束，引用账户表
);
# 创建 INSERT 型触发器
-- 更改存储过程的结束符为$$
DELIMITER $$
-- 创建一个触发器，在accounts表插入数据后触发
CREATE TRIGGER after_account_insertAFTER INSERTON accountsFOR EACH ROW
BEGIN-- 将插入操作记录到account_change_log表中INSERT INTO account_change_log (account_id, operation_type, new_value)VALUES (NEW.id, 'INSERT', NEW.username); -- 记录新增账户的用户名
END $$
-- 复原存储过程的结束符为默认的;
DELIMITER ;# 创建 UPDATE 型触发器
-- 再次更改存储过程的结束符为$$
DELIMITER $$
-- 创建一个触发器，在accounts表更新数据后触发
CREATE TRIGGER after_account_updateAFTER UPDATEON accountsFOR EACH ROW
BEGIN-- 将更新操作记录到account_change_log表中INSERT INTO account_change_log (account_id, operation_type, old_value, new_value)VALUES (OLD.id, 'UPDATE', OLD.username, NEW.username); -- 记录更新前后的用户名
END $$
-- 复原存储过程的结束符为默认的;
DELIMITER ;-- 创建 DELETE 型触发器
-- 再次更改存储过程的结束符为$$
DELIMITER $$
-- 创建一个触发器，在accounts表删除数据后触发
CREATE TRIGGER after_account_deleteAFTER DELETEON accountsFOR EACH ROW
BEGIN-- 将删除操作记录到account_change_log表中INSERT INTO account_change_log (account_id, operation_type, old_value)VALUES (OLD.id, 'DELETE', OLD.username); -- 记录删除账户的用户名
END $$
-- 复原存储过程的结束符为默认的;
DELIMITER ;
# 插入测试：执行上述插入操作后，可以查看 account_change_log 表，确认新用户的插入记录是否正确。
INSERT INTO accounts (username, password, email) VALUES
('user1', 'password1', 'user1@example.com'),
('user2', 'password2', 'user2@example.com'),
('user3', 'password3', 'user3@example.com');
# 更新测试：更新某个用户的信息，查看 account_change_log 表，确认更新的记录是否正确。
UPDATE accounts SET username = 'updated_user1' WHERE id = 1; 
# 删除测试：删除某个用户，查看 account_change_log 表，确认删除的记录是否正确。
DELETE FROM accounts WHERE id = 2;参考资料
-
MySQL 官方文档 - 触发器