MySQL触发器

一、含义

触发器是由事件触发而自动执行某个操作，这个事件包括INSERT语句、UPDATE语句和DELETE语句。触发器是特殊的储存过程，而不同于储存过程，储存过程可以通过存储名字调用。

二、特点

触发事件的操作和触发器里的SQL语句是一个事务操作，具有原子性,要么全部执行，要么都不执行。

三、案例运行环境

MySQL：8.0.2

# 案例两张表
MySQL [test]> SELECT * FROM tb_user;
+----+----------+----------+---------------------+
| id | realname | integral | cretaed_at          |
+----+----------+----------+---------------------+
|  1 | 王维     |       10 | 2020-01-06 15:17:41 |
|  2 | 李白     |        0 | 2020-01-06 15:14:06 |
+----+----------+----------+---------------------+
2 rows in set (0.00 sec)

MySQL [test]> SELECT * FROM tb_article;
+----+-----+--------+---------------------------------------------------------------------------+---------------------+
| id | uid | title  | content                                                                   | created_at          |
+----+-----+--------+---------------------------------------------------------------------------+---------------------+
|  1 |   1 | 相思   | 红豆生南国，春来发几枝。 愿君多采撷，此物最相思。                         | 2020-01-06 13:24:26 |
+----+-----+--------+---------------------------------------------------------------------------+---------------------+
1 row in set (0.01 sec)

 

四、创建触发器

1. 创建触发器语法

   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

    

2. 参数解释 

   trigger_name：触发器名称。
   trigger_time：触发器执行时间；BEFORE表示在触发器事件之前执行触发器语句，AFTER表示在触发器事件之后执行触发器语句。
   trigger_event：触发器事件，即触发器执行条件，包含INSERT、UPDATE、DELETE语句。
   tbl_name：触发事件的操作表名。
   FOR EACH ROW：任何一条记录上的操作满足出发事件都会触发该触发器。
   trigger_order：多个触发器作用同一张表，可以定义执行顺序。
   trigger_body：激活触发器后被执行的语句。
   
   

    

3.  创建触发器

   DELIMITER //
   CREATE TRIGGER tri_integral
   AFTER INSERT
   ON tb_article FOR EACH ROW
   BEGIN
   	DECLARE uid INT;
           --获取用户ID
   	SET uid= NEW.uid;
   	UPDATE tb_user SET integral = integral + 10 WHERE id = uid;
   END;
   //
   DELIMITER ;
   
   # 发布一篇文章，用户积分自动增加10；
   MySQL [test]> INSERT INTO `tb_article` (`uid`, `title`, `content`) VALUES ('1', '画', '远看山有色，近听水无声。春去花还在，人来鸟不惊。');
   Query OK, 1 row affected (0.01 sec)
   
   MySQL [test]> SELECT * FROM tb_user;
   +----+----------+----------+---------------------+
   | id | realname | integral | cretaed_at          |
   +----+----------+----------+---------------------+
   |  1 | 王维     |       20 | 2020-01-06 15:17:41 |
   |  2 | 李白     |        0 | 2020-01-06 15:14:06 |
   +----+----------+----------+---------------------+
   2 rows in set (0.00 sec)

    

五、查看触发器 

1. 通过SHOW TRIGGERS语句查看触发器

   MySQL [test]> SHOW TRIGGERS \G;
   *************************** 1. row ***************************
                Trigger: tri_integral
                  Event: INSERT
                  Table: tb_article
              Statement: BEGIN
   DECLARE uid INT;
   SET uid= NEW.uid;
   UPDATE tb_user SET integral = integral + 10 WHERE id = uid;
   END
                 Timing: AFTER
                Created: 2020-01-06 15:16:05.49
               sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                Definer: root@%
   character_set_client: utf8mb4
   collation_connection: utf8mb4_0900_ai_ci
     Database Collation: utf8_general_ci
   1 row in set (0.00 sec)

    

2. 通过information_schema.TRIGGERS查看触发器 

   MySQL [test]> SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = 'tri_integral' \G;
   *************************** 1. row ***************************
              TRIGGER_CATALOG: def
               TRIGGER_SCHEMA: test
                 TRIGGER_NAME: tri_integral
           EVENT_MANIPULATION: INSERT
         EVENT_OBJECT_CATALOG: def
          EVENT_OBJECT_SCHEMA: test
           EVENT_OBJECT_TABLE: tb_article
                 ACTION_ORDER: 1
             ACTION_CONDITION: NULL
             ACTION_STATEMENT: BEGIN
   DECLARE uid INT;
   SET uid= NEW.uid;
   UPDATE tb_user SET integral = integral + 10 WHERE id = uid;
   END
           ACTION_ORIENTATION: ROW
                ACTION_TIMING: AFTER
   ACTION_REFERENCE_OLD_TABLE: NULL
   ACTION_REFERENCE_NEW_TABLE: NULL
     ACTION_REFERENCE_OLD_ROW: OLD
     ACTION_REFERENCE_NEW_ROW: NEW
                      CREATED: 2020-01-06 15:16:05.49
                     SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                      DEFINER: root@%
         CHARACTER_SET_CLIENT: utf8mb4
         COLLATION_CONNECTION: utf8mb4_0900_ai_ci
           DATABASE_COLLATION: utf8_general_ci
   1 row in set (0.01 sec)
   

    

六、删除触发器 

1. 使用DROP TRIGGER删除

   MySQL [test]> DROP TRIGGER tri_integral;
   Query OK, 0 rows affected (0.01 sec)

    

七、触发器优缺点 

1. 优点
   1. 触发器可以加强数据库表中数据的完整性约束和业务规则；
   2. 同步实时复制表中数据；
2. 缺点
   1. 增加了程序的复杂性；

八、其他

MySQL触发器关键字：NEW 和 OLD;
使用方法： NEW.columnName （columnName 为相应数据表某一列名）

INSERT型触发器：NEW代表要插入的新数据；
UPDATE型触发器：OLD代表要修改的原数据，NEW表示要修改为的新数据；
DELETE型触发器：OLD表示要删除的原数据；

OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值。