MySQL触发器介绍

古城微笑少年丶 2022-08-29 10:42 83阅读 0赞

**前言：**

在学习 MySQL 的过程中，可能你了解过触发器的概念，不清楚各位是否有详细的去学习过触发器，最近看了几篇关于触发器的文档，分享下 MySQL 触发器相关知识。

#### 1.触发器简介 ####

触发器即 triggers ，它是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。

参考官方文档，触发器创建语法模板如下：

CREATE
        [DEFINER = user]
        TRIGGER trigger_name
        trigger_time trigger_event
        ON tbl_name FOR EACH ROW
        [trigger_order]
        trigger_body
    
    trigger_time: { BEFORE | AFTER }
    trigger_event: { INSERT | UPDATE | DELETE }
    trigger_order: { FOLLOWS | PRECEDES } other_trigger_name

触发器只能创建在永久表上，不能对临时表或视图创建触发器。触发器的名称在单个数据库内是唯一的。参考上面创建语句，触发器创建有几点要素，下面简要说明下：

**trigger\_time**：是触发动作时间，可以是 BEFORE 或 AFTER ，表示触发器在要修改的每一行之前或之后激活。

**trigger\_event**：指示激活触发器的操作类型。这些 trigger\_event 值是被允许的：

*  insert：只要向表中插入新行，触发器就会激活。例如 insert 、load data、replace 语句。
 *  update：更改表中某一行数据时激活触发器。例如 update 语句。
 *  delete：从表中删除某一行数据时激活触发器。例如 delete 和 replace 语句。表上的 DROP TABLE 和 TRUNCATE TABLE 语句不会激活此触发器，因为它们不使用 delete ，删除分区也不会激活 delete 触发器。

**trigger\_body**：是触发器激活时要执行的语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。在触发器主体中，可以使用 old 和 new 来引用触发器中发生变化的记录内容。

#### 2.触发器具体操作 ####

下面来看下触发器相关的具体操作：

# 创建表 创建触发器
    mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
    Query OK, 0 rows affected (0.03 sec)
    mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
    
    mysql> delimiter //
    mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
           FOR EACH ROW
           BEGIN
               IF NEW.amount < 0 THEN
                   SET NEW.amount = 0;
               ELSEIF NEW.amount > 100 THEN
                   SET NEW.amount = 100;
               END IF;
           END;//
    mysql> delimiter ;
    
    # 验证触发器作用
    mysql> select * from account;
    +----------+---------+
    | acct_num | amount  |
    +----------+---------+
    |      137 |   14.98 |
    |      141 | 1937.50 |
    |       97 | -100.00 |
    +----------+---------+
    3 rows in set (0.00 sec)
    
    mysql> update account set amount = 114.98 where acct_num = 137;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from account;
    +----------+---------+
    | acct_num | amount  |
    +----------+---------+
    |      137 |  100.00 |
    |      141 | 1937.50 |
    |       97 | -100.00 |
    +----------+---------+
    3 rows in set (0.00 sec)
    
    # 查看触发器
    mysql> show triggers;
    
    # 删除触发器
    mysql> drop trigger if exists upd_check;
    
    # 查看数据库实例中所有触发器
    SELECT
    	a.TRIGGER_SCHEMA,
    	a.TRIGGER_NAME,
    	a.ACTION_TIMING,
    	a.EVENT_OBJECT_TABLE,
    	a.EVENT_MANIPULATION 
    FROM
    	information_schema.`TRIGGERS` a 
    WHERE
    	a.TRIGGER_SCHEMA NOT IN ( 'information_schema', 'performance_schema', 'mysql', 'sys' );
      
    delimiter // 设置MySQL执行结束标志，默认为;

上面展示了一些关于触发器的基本操作，其实触发器在生产环境中还是比较少见的，即使它能解决我们某些数据库需求，因为触发器的使用存在一系列的缺点，简要总结几点缺点如下：

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

但是触发器也并不是一无用处，比如我们不想让人删除或更新这个表的数据，可以用触发器实现，下面的一些场景可能对你有所启发：

# 禁止删除数据 即使你有权限
    mysql> select * from student;
    +--------------+------+--------+-------+-------+
    | increment_id | s_id | s_name | s_sex | s_age |
    +--------------+------+--------+-------+-------+
    |            1 | 1001 | sdfsd  | 男    |    18 |
    |            2 | 1003 | zsdfsd | 女    |    19 |
    +--------------+------+--------+-------+-------+
    2 rows in set (0.00 sec)
    
    mysql> delimiter //
    mysql> CREATE TRIGGER `tri_delstu` BEFORE DELETE ON `student` FOR EACH ROW begin
        -> declare msg varchar(255);
        -> set msg="不允许删除学生信息";
        -> SIGNAL SQLSTATE 'HY000' SET  MESSAGE_TEXT = msg;
        -> end; //
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> delimiter ;
    mysql> delete from student where s_id = 1003;
    ERROR 1644 (HY000): 不允许删除学生信息
    
    # 禁止更新某个字段
    mysql> delimiter //
    mysql> CREATE TRIGGER trg__updateSid BEFORE UPDATE ON `student`
        -> FOR EACH ROW
        -> BEGIN
        ->  DECLARE msg VARCHAR(100); 
        ->  IF NEW.s_id <> OLD.s_id THEN
        ->  SET msg='不允许修改学号'; 
        ->  SIGNAL SQLSTATE 'HY000' SET message_text = msg; 
        ->  END IF; 
        -> END; //
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> delimiter ;
    mysql> update student set s_id = 1002 where increment_id = 2;
    ERROR 1644 (HY000): 不允许修改学号
    
    # 限制修改范围
    mysql> delimiter //
    mysql> CREATE TRIGGER `tri_update_age` BEFORE UPDATE ON `student` FOR EACH ROW BEGIN
        ->         DECLARE msg VARCHAR(20);
        ->   IF (NEW.s_age<0) THEN 
        ->         set msg="年龄不能小于0";
        ->   signal sqlstate 'HY000' set message_text=msg;
        ->         END IF;
        -> END; //
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> delimiter ;
    mysql> update student set s_age=10 where s_id = 1001;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> update student set s_age=-10 where s_id = 1001;
    ERROR 1644 (HY000): 年龄不能小于0

**总结：**

本篇文章简要介绍了触发器的定义及使用案例。在业务逻辑复杂或表变动比较频繁的系统还是不推荐使用触发器，当然它也是有自己的应用场景，无论怎样，触发器的逻辑总是越简单越好，我们应该让数据库做它擅长做的事，不能想着所有逻辑都在数据库层面实现。