致力于分享mysql相关知识,收集mysql相关工具!

mysql菜鸟教程

专栏导航

16.2 创建触发器

    在了解了触发器的概念和应用场景后，本节将详细介绍如何在 MySQL 中创建触发器。我们将从基本语法开始，通过多个示例逐步深入，并讲解创建过程中的注意事项。

一、创建触发器的语法

CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名
FOR EACH ROW
触发器体;

语法解析

部分

说明

触发器名

触发器的名称，在数据库中应唯一，通常采用 

{触发时间}_{触发事件}_{表名}

 的命名方式，例如 

before_insert_students

。

触发时间

BEFORE

 或 

AFTER

，指定在事件之前或之后执行。

触发事件

INSERT

、

UPDATE

 或 

DELETE

，指定触发操作的类型。

ON 表名

触发器所属的表。

FOR EACH ROW

表示触发器对每一行受到影响的操作都会执行一次。对于批量操作（如 

INSERT ... SELECT

、

UPDATE

 多行），触发器会对每一行分别执行。

触发器体

触发器执行的 SQL 语句。如果只有一条语句，可以直接写；如果有多条语句，必须使用 

BEGIN ... END

 包裹，并且需要临时修改分隔符（

DELIMITER

）。

二、准备：修改分隔符

在 MySQL 中，默认的语句分隔符是分号 ;。但在触发器体内可能会包含多条 SQL 语句，每条都以 ; 结尾。为了让 MySQL 正确地将整个触发器定义视为一个完整的语句，我们需要在创建触发器之前临时修改分隔符。

DELIMITER //   -- 将分隔符改为 //

CREATE TRIGGER 触发器名
...
BEGIN
    ...;   -- 内部语句仍以 ; 结尾
    ...;
END //

DELIMITER ;   -- 恢复分隔符为 ;

这是一个固定模式，所有包含 BEGIN ... END 块的触发器定义都需要这样做。

示例1：记录插入日志

假设我们有一个 students 表，希望在每次插入新学生时自动记录一条日志到 student_log 表。

首先创建日志表：

CREATE TABLE student_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT NOT NULL,
    action VARCHAR(20) NOT NULL,
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建触发器：

DELIMITER //

CREATE TRIGGER after_insert_students
AFTER INSERT ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_log (student_id, action) VALUES (NEW.id, 'INSERT');
END //

DELIMITER ;

解释：

  • AFTER INSERT：在插入成功之后触发。

  • FOR EACH ROW：对于每一行新插入的数据，都会执行一次 BEGIN ... END 中的内容。

  • NEW.id：NEW 代表即将插入（或刚插入）的新行，NEW.id 就是新行的 id 列值。对于自增列，NEW.id 是在插入后生成的值，在 BEFORE INSERT 触发器中可能为 0 或 NULL。

测试：

INSERT INTO students (name, age) VALUES ('张三', 20);
SELECT * FROM student_log;

示例2：数据验证（BEFORE INSERT）

在插入数据前检查年龄是否合法，如果非法则阻止操作。

DELIMITER //

CREATE TRIGGER before_insert_students
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    IF NEW.age < 0 OR NEW.age > 150 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0到150之间';
    END IF;
END //

DELIMITER ;

说明：

  • BEFORE INSERT：在插入之前触发，有机会修改 NEW 的值或阻止插入。

  • SIGNAL 语句用于抛出自定义错误，导致当前操作失败并回滚。

测试：

INSERT INTO students (name, age) VALUES ('李四', 200);  -- 将被拒绝，并显示错误消息

示例3：自动计算列（BEFORE INSERT/UPDATE）

假设有一个 order_items 表，包含 quantity、unit_price 和 total_price 列。我们希望 total_price 自动计算为 quantity * unit_price。

DELIMITER //

CREATE TRIGGER before_insert_order_items
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    SET NEW.total_price = NEW.quantity * NEW.unit_price;
END //

DELIMITER ;

对于更新操作，同样需要维护该列：

DELIMITER //

CREATE TRIGGER before_update_order_items
BEFORE UPDATE ON order_items
FOR EACH ROW
BEGIN
    SET NEW.total_price = NEW.quantity * NEW.unit_price;
END //

DELIMITER ;

示例4：记录更新和删除的旧值（AFTER UPDATE / AFTER DELETE）

使用 OLD 关键字访问更新前的值或删除的值。

记录更新前和更新后的姓名变化：

CREATE TABLE students_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    old_name VARCHAR(100),
    new_name VARCHAR(100),
    change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE TRIGGER after_update_students
AFTER UPDATE ON students
FOR EACH ROW
BEGIN
    IF OLD.name != NEW.name THEN
        INSERT INTO students_audit (student_id, old_name, new_name)
        VALUES (OLD.id, OLD.name, NEW.name);
    END IF;
END //

DELIMITER ;

记录删除的数据：

DELIMITER //

CREATE TRIGGER after_delete_students
AFTER DELETE ON students
FOR EACH ROW
BEGIN
    INSERT INTO student_log (student_id, action) VALUES (OLD.id, 'DELETE');
END //

DELIMITER ;

三、查看已有触发器

-- 查看当前数据库中的所有触发器
SHOW TRIGGERS;

-- 查看特定触发器的定义
SHOW CREATE TRIGGER 触发器名;

四、删除触发器

DROP TRIGGER [IF EXISTS] 触发器名;

五、创建触发器的注意事项

1、同一个表不能有两个相同的触发时间和事件组合。例如，不能有两个 AFTER INSERT 触发器，但可以有 BEFORE INSERT 和 AFTER INSERT 各一个。

2、触发器主体中不能使用：

  • 显式或隐式地开始或结束事务的语句（如 START TRANSACTION、COMMIT、ROLLBACK）。

  • 返回结果集的语句（如普通 SELECT 不赋值给变量）。

  • 对自身表进行修改的语句，可能引发递归（除非合理控制）。

3、递归限制：MySQL 默认不允许递归调用触发器（max_sp_recursion_depth = 0）。如果触发器内又操作了同一张表，可能导致错误或死循环。

4、性能影响：触发器会增加 DML 操作的开销，对于频繁操作的表，应谨慎使用。

5、使用 SIGNAL 抛错：在 BEFORE 触发器中，如果条件不满足，可以使用 SIGNAL 语句抛出错误，阻止操作继续。

6、NEW 和 OLD 的访问权限：

  • 在 BEFORE 触发器中，可以修改 NEW 的列值（但不能修改 OLD）。

  • 在 AFTER 触发器中，不能修改数据，只能读取。

7、字符集和校对规则：触发器内的字符串比较可能与表定义不同，需要注意一致性。

六、综合练习

创建一个 products 表和一个 product_price_history 表，每当 products 表中的价格发生变化时，自动记录旧价格、新价格和修改时间。

表结构：

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

CREATE TABLE price_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

触发器：

DELIMITER //

CREATE TRIGGER after_update_products
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO price_history (product_id, old_price, new_price)
        VALUES (OLD.id, OLD.price, NEW.price);
    END IF;
END //

DELIMITER ;

测试：

INSERT INTO products (name, price) VALUES ('手机', 2999.00);
UPDATE products SET price = 2799.00 WHERE id = 1;
SELECT * FROM price_history;

小结

  • 创建触发器使用 CREATE TRIGGER 语句，需指定触发时间、事件、表名和主体。

  • 对于多语句主体，必须使用 DELIMITER 修改分隔符，并用 BEGIN ... END 包裹。

  • 使用 NEW 和 OLD 访问行数据，BEFORE 触发器中可修改 NEW。

  • 可用 SHOW TRIGGERS 查看，用 DROP TRIGGER 删除。

  • 触发器强大但需谨慎使用，注意性能和递归问题。


所有评论

↑
关于我 备案号:蜀ICP备2023042032号-1