MySQL—触发器详解

基本介绍

触发器是与表有关的数据库对象，在 INSERT、UPDATE、DELETE 操作之前或之后触发并执行触发器中定义的 SQL 语句。

触发器的这种特性可以协助应用在数据库端确保数据的完整性、日志记录、数据校验等操作。

使用别名 NEW 和 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,                                 -- 账户ID
    username   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,      -- 日志ID
    account_id     INT,                                 -- 被更改的账户ID
    operation_type VARCHAR(10),                         -- 操作类型：INSERT, UPDATE, DELETE
    old_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_insert
    AFTER INSERT
    ON accounts
    FOR 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_update
    AFTER UPDATE
    ON accounts
    FOR 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_delete
    AFTER DELETE
    ON accounts
    FOR 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 官方文档 - 触发器