Trigger

🗞️ 返回专题页

触发器

• 定义：MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（ INSERT， DELETE 或 UPDATE）时就会激活它执行。

• 作用：触发器与数据表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性、日志记录 , 数据校验等操作。

优缺点

触发器的优点如下：

• 触发器的执行是自动的，当对触发器相关表的数据做出相应的修改后立即执行。

• 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。

• 触发器可以实现表数据的级联更改，在一定程度上保证了数据的完整性。

触发器的缺点如下：

• 使用触发器实现的业务逻辑在出现问题时很难进行定位，特别是涉及到多个触发器的情况下，会使后期维护变得困难。
• 大量使用触发器容易导致代码结构被打乱，增加了程序的复杂性，
• 如果需要变动的数据量较大时，触发器的执行效率会非常低。

触发器的类型

• 在实际使用中， MySQL 所支持的触发器有三种： INSERT 触发器、 UPDATE 触发器和 DELETE 触发器。
• 使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

INSERT只有NEW，因为OLD对于插入这个操作无意义；

UPDATE有OLD也有NEW；

DELETE只有OLD，因为删除之后的哪里还有什么东西。

基础语法

• 创建

CREATE TRIGGER trigger_name 
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name FOR EACH ROW <触发器主体>

触发器主体：触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。

FOR EACH ROW：一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

• 查看

SHOW TRIGGERS;

在 MySQL 中，所有触发器的信息都存在 information_schema 数据库的 triggers 表中，可以通过查询命令 SELECT 来查看:
SELECT * FROM information_schema.triggers WHERE trigger_name= 'trigger_name';

• 删除

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

INSERT触发器

在 INSERT 语句执行之前或之后响应的触发器，使用 INSERT 触发器需要注意以下几点：

1. 更多操作在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。

   2.在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。

   3.对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

/*通过触发器记录emp表的数据变更日志(user_logs)*/
 -- 创建日志表user_logs
 CREATE TABLE user_logs (
  id INT ( 11 ) PRIMARY KEY auto_increment,
  opration VARCHAR ( 20 ) NOT NULL,
  oprate_time DATETIME NOT NULL,
  oprate_id INT ( 11 ) NOT NULL,
  oprate_params VARCHAR ( 500 ) NOT NULL 
) ENGINE = innodb DEFAULT CHARSET = utf8;

-- 插入数据触发器
CREATE TRIGGER emp_insert_trigger AFTER INSERT ON index_mysql.emp FOR EACH ROW
BEGIN
    INSERT INTO user_logs ( id, opration, oprate_time, oprate_id, oprate_params )
  VALUES
    (NULL,'INSERT',NOW(),NEW.id,concat('插入数据内容为,id =',new.id,',name = ',new.name,
    ,age = ',new.age,',profession =',new.profession,',sal = ',new.sal,',intime = ',new.intime,
    ',magid = ',new.magid,',dept_id = ',new.dept_id));
END //                                       

UPTATE触发器

• 在 UPDATE 语句执行之前或之后响应的触发器，使用 UPDATE 触发器需要注意以下几点：

  1. 在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。

  2. 在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。

  3. 在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。

  4. OLD 中的值全部是只读的，不能被更新。

CREATE TRIGGER emp_update_after_trigger AFTER UPDATE ON index_mysql.emp FOR EACH ROW
BEGIN   --创建触发器emp_update_after_trigger，触发时机为更改index_mysql库中的emp表,为行级触发器。
    INSERT INTO user_logs
  VALUES
    (
      NULL,		--自增的情况用NULL.
      'UPDATE',
      NOW(),
      new.id,   --更新的新数据的id值
      concat(
        '旧的数据内容为,id =',
        old.id,
        ',name = ',
        old.NAME,
        ',age = ',
        old.age,
        ',profession =',
        old.profession,
        ',sal = ',
        old.sal,
        ',intime = ',
        old.intime,
        ',magid = ',
        old.magid,
        ',dept_id = ',
        old.dept_id,
        '|新的数据内容为,id =',
        new.id,
        ',name = ',
        new.NAME, 
        ',age = ',
        new.age,
        ',profession =',
        new.profession,
        ',sal = ',
        new.sal,
        ',intime = ',
        new.intime,
        ',magid = ',
        new.magid,
        ',dept_id = ',
        new.dept_id 
      ));
END //

DELETE触发器

• DELETE 语句执行之前或之后响应的触发器，使用 DELETE 触发器需要注意以下几点：
  1. 在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
  2. OLD 中的值全部是只读的，不能被更新。

CREATE TRIGGER emp_delete_after_trigger AFTER DELETE ON index_mysql.emp FOR EACH ROW
BEGIN	--创建一个emp_delete_after_trigger触发器，在删除index_mysql库中的emp表中的数据之后触发的行级事务。
    INSERT INTO user_logs
  VALUES
    (
      NULL,
      'DELETE',
      NOW(),
      OLD.id,		--DELETE触发器只有OLD。
      concat(
        '删除之前的数据：id = ',
        old.id,
        ',name = ',
        old.NAME,
        ',age = ',
        old.age,
        ',prodession = ',
        old.profession,
        ',sal = ',
        old.sal,
        ',intime = ',
        old.intime,
        ',magid = ',
        old.magid,
        ',dept_id = ',
        old.dept_id 
      ));
  
END;

AFTER

触发时机为在表中数据发生改变后的状态；上面的例子大都以AFTER为主，就不讲了。

BEFORE

BEFORE触发器是指触发器在所监视的触发事件执行之前激活，激活后执行的操作先于监视的事件，这样就有机会进行一些判断，或修改即将发生的操作。

BEFORE触发器也可以根据监视事件分为三种，分别是INSERT型、UPDATE型和DELETE型。

INSERT

• 给teacher表创建一个列， salary列，记录教师的工资，建一个触发器t_d_t，插入教师信息时，如果教师工资小于3000，则自动调整成3000

create trigger t_d_t before insert on teacher for each row
begin
	if new.salary < 3000 then set new.salary = 3000;
	end if;
end;

UPDATE

• 给grade表建立一个学分列，并创建一个触发器，当修改grade表中数据时，如果修改后的成绩小于60分，则触发器将该成绩对应的课程学分修改为0，否则将学分改成对应课程的学分。

create trigger t_g_t before update on grade for each row
begin
	declare xf int;
	select credit into xf from course where courseid = new.courseid;
	if new.grade < 60 then set new.credit = 0;
	else set new.credit = xf;
	end if;
end;

别问为什么没有DELETE，问就是没意义。

BEFORE和AFTER的区别

BEFORE(insert,update)可以对于伪NEW进行修改来达到符合自己需求的触发；

AFTER则不可以，对于AFTER来说，伪NEW数据就是NEW数据；

OLD 在触发器中是只读的，无法被修改，无论是在 BEFORE 还是 AFTER 触发器中。

总结

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

1. 对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

2. 若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

3. 若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

4. 仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行 AFTER 触发程序。

5. 触发器只能创建在永久表上，不能对临时表创建触发器；

6. 触发器不能使用CALL语句调用具有返回值或使用了动态SQL的存储过程（存储过程可以使用OUT或INOUT参数返回给触发器返回值）。

7. 触发器中不能使用开启或结束事务的语句段，比如，开始事务（START TRANSACTION）、提交事务（COMMIT）或是回滚事务（ROLLBACK），但是回滚到一个保存点（SAVEPOINT是允许的，因为回滚到保存点不会结束事务）；

8. 外键不会激活触发器；

9. 当使用基于行的复制时，从表上的触发器不会因操作主表中的数据而激活。当使用基于语句的复制时，从表上的触发器会被激活。参考 Section 17.4.1.34,“Replication and Triggers”；

10. 触发器中不允许返回值，因此触发器中不能有返回语句，如果要立即停止一个触发器，应该使用LEAVE语句；

例题

假设有一个员工管理系统，包含两个表：employees 和 employees_audit。

• employees 表用于存储员工信息，结构如下

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2),	--一个整体数字长度为10，小数点长度为2的数；
    hire_date DATE
);

• employees_audit 表用于记录员工的更新操作，结构如下：

CREATE TABLE employees_audit (
    audit_id INT PRIMARY KEY AUTO_INCREMENT,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP  --类型为时间戳，默认为当前时间戳；
);

要求：

编写一个 AFTER UPDATE 触发器，要求：

1. 当 employees 表中的员工 薪资（salary） 被更新时，将旧的薪资和新的薪资记录到 employees_audit 表中。
2. 如果更新的 salary 没有变化，则不需要记录。

解答思路：

1. 创建触发器，监控 employees 表中的 salary 字段的变化。
2. 当触发器检测到 salary 字段发生变化时，将旧的薪资和新的薪资插入到 employees_audit 表中。

DELIMITER $

CREATE TRIGGER after_employee_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  -- 检查是否有 salary 变化
  IF OLD.salary != NEW.salary THEN
    -- 插入变更记录到 employees_audit 表
    INSERT INTO employees_audit (employee_id, old_salary, new_salary)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary);
  END IF;
END$

DELIMITER ;

复杂例题

在一个电子商务系统中，我们有三个表：

1. products：存储商品信息。
2. orders：存储订单信息。
3. order_items：存储每个订单的商品信息，包括商品的数量。

我们希望创建一个触发器，当往 order_items 表中插入一条新记录时，它会自动：

1. 检查商品库存是否足够。
2. 如果库存不足，触发器应拒绝插入，并抛出错误。
3. 如果库存足够，则扣减 products 表中的库存数量。
4. 更新 orders 表中的订单总金额。

1.products 表：

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    stock INT
);

2.orders 表：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    total_amount DECIMAL(10, 2) DEFAULT 0
);

3.order_items 表：

CREATE TABLE order_items (
    order_item_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

创建思路

我们需要创建一个在 order_items 表上运行的 BEFORE INSERT 触发器，来实现以下逻辑：

1. 检查 products 表中对应的商品库存是否足够。
2. 如果库存不足，抛出错误，阻止插入操作。
3. 如果库存足够，更新 products 表中的库存数量。
4. 更新 orders 表中的 total_amount 字段，计算该订单的总金额。

DELIMITER $

CREATE TRIGGER before_order_item_insert
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    DECLARE product_price DECIMAL(10, 2);
    DECLARE new_total_amount DECIMAL(10, 2);

    -- 检查商品库存
    SELECT stock INTO current_stock
    FROM products
    WHERE product_id = NEW.product_id;
    
    IF current_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Not enough stock for this product';
    END IF;

    -- 获取商品的价格
    SELECT price INTO product_price
    FROM products
    WHERE product_id = NEW.product_id;
    
    -- 设置新插入项的价格
    SET NEW.price = product_price;

    -- 扣减库存
    UPDATE products
    SET stock = stock - NEW.quantity
    WHERE product_id = NEW.product_id;
    
    -- 计算新的订单总金额
    SELECT total_amount + (NEW.quantity * product_price) INTO new_total_amount
    FROM orders
    WHERE order_id = NEW.order_id;

    -- 更新订单的总金额
    UPDATE orders
    SET total_amount = new_total_amount
    WHERE order_id = NEW.order_id;
END$

DELIMITER ;

注解：

1. BEFORE INSERT：这个触发器在 order_items 表上插入新记录之前触发。
2. DECLARE 语句：声明局部变量，如 current_stock（当前库存）、product_price（商品价格）、new_total_amount（新的订单总金额）。
3. 库存检查：通过查询 products 表来检查插入的商品是否有足够的库存，如果库存不足，则通过 SIGNAL 抛出错误，阻止插入操作。
4. 更新库存：如果库存足够，触发器会更新 products 表中对应商品的库存，减少该次订单购买的数量。
5. 计算订单总金额：每次插入订单项时，计算新商品的总价，并将其加入 orders 表中的 total_amount 字段。
6. SIGNAL：用于抛出自定义错误，阻止插入操作。SQLSTATE '45000' 是用户定义的通用错误码。

示例数据：

INSERT INTO products (product_id, product_name, price, stock) VALUES
(1, 'Laptop', 1200.00, 10),
(2, 'Smartphone', 800.00, 20),
(3, 'Tablet', 500.00, 15);

INSERT INTO orders (order_id, customer_id, total_amount) VALUES
(1, 101, 0),
(2, 102, 0);

通过这个触发器，系统可以在订单插入时自动进行库存管理和订单金额的更新，确保数据一致性。