【MySQL 触发器的操作】

程小雾

已于 2023-03-27 20:45:01 修改

阅读量1.1k

点赞数 2

分类专栏： MySQL 学习文章标签： mysql 数据库 java

于 2023-02-18 19:33:05 首次发布

本文链接：https://blog.csdn.net/m0_56767169/article/details/124860972

版权

MySQL 学习专栏收录该内容

15 篇文章 1 订阅

订阅专栏

一、触发器的概念

触发器（TRIGGER）是 MySQL 的数据库对象之一，用来实现由一些表事件触发的某个操作。该对象与编程语言中的函数非常类似，都需要声明、执行等。但触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。

为什么使用触发器

在具体开发项目时，经常会遇到如下实例：

在学生表中拥有字段：学生名字、学生总数，每当添加一条关于学生的记录，学生的总数就必须同时改变。
在顾客信息表中拥有字段：顾客名称、电话、地址缩写，每当添加一条顾客的记录时，都需要检查电话号码格式是否正确，顾客地址缩写是否正确。

上述实例有个共同之处，即都需要在表发生改变时，自动进行一些处理。例如，对于第一个实例，可以创建一个触发器对象，每次添加一条学生记录时，就执行一次计算学生总数的操作。

MySQL 中能够激活触发器的语句

DELETE 语句
INSERT 语句
UPDATE 语句

触发器操作的分类

创建触发器
查看触发器
删除触发器

二、创建触发器

按照激活触发器时所执行的语句数目，可以将触发器分为“一个执行语句的触发器” 和 “多个执行语句的触发器”。

创建有一条执行语句的操作

语法形式

CREATE TRIGGER trigger_name
	BEFORE|AFTER trigger_EVENT
		ON TABLE_NAME FOR EACH ROW trigger_STMT

元素	含义
trigger_name	所要创建的触发器的名字
BEFORE/AFTER	触发器执行的时机（在事件之前/之后）
trigger_EVENT	触发器执行条件（包含DELETE、INSERT 和 UPDATE）
TABLE_NAME	触发器事件操作表的名字
FOR EACH ROW	表示任何一条记录上的操作满足触发事件都会触发该触发器
trigger_STMT	激活触发器后被执行的语句

【实例】
执行SQL 语句 CREATE TRIGGER，在数据库 company 中存在两个表对象：部门表（t_dept）和日记表（t_diary），创建触发器实现：向部门表中插入记录时，就会在插入之前向日记表中插入当前时间。
1、用前面的知识创建两个表格
建立部门表： t_dept

mysql> #建立表#
mysql> CREATE TABLE t_dept(
    -> deptno INT,
    -> dname VARCHAR(20),
    -> loc VARCHAR(40)
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> #查询表#
mysql> DESC t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int         | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

建立日记表 t_diary

mysql> #建立表#
mysql> CREATE TABLE t_diary(
    -> diaryno INT(11) PRIMARY KEY  AUTO_INCREMENT,
    -> tablename varchar(20),
    -> diarytime datetime
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> #查询表#
mysql> DESC t_diary;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| diaryno   | int         | NO   | PRI | NULL    | auto_increment |
| tablename | varchar(20) | YES  |     | NULL    |                |
| diarytime | datetime    | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

2、创建触发器----一条执行语句
建立名为 tri_diarytime 的触发器用于在部门表 t_dept 插入任意一条记录时，就会在插入操作之前向表 t_diary 中插入当前的时间记录

mysql>  #创建触发器#
mysql> CREATE TRIGGER tri_diarytime
    ->     before insert
    ->            on t_dept for each row
    ->                   insert into t_diary values(null,'t_dept',now());

Query OK, 0 rows affected (0.02 sec)

3、验证
效验触发器 tri_dearytime 的功能
向表 t_dept 中插入一条记录，然后查看表 t_diary 中是否执行插入当前时间操作。

mysql> # 向表t_dept中插入一条记录 #
mysql> INSERT INTO t_dept VALUES(1,'cjgongdept','Shangxi');
Query OK, 1 row affected (8.24 sec)

mysql> #查看表 t_diary 中的记录 #
mysql> SELECT *
    -> FROM t_diary;
+---------+-----------+---------------------+
| diaryno | tablename | diarytime           |
+---------+-----------+---------------------+
|       1 | t_dept    | 2022-09-03 22:13:43 |
+---------+-----------+---------------------+
1 row in set (0.00 sec)

创建包含多条执行语句的触发器

语法形式

create trigger trigger_name
	BEFORE|AFTER trigger_event
		ON TABLE_NAME FOR EACH ROW
			BEGIN
			trigger_STMT
			END

在上述语句中，比 “ 只有一条执行语句的触发器”语法多出来两个关键字 “ BEGIN ” 和 “ END ” ,在这两个关键字之间是所要执行的多个执行语句的内容，他们之间用“；”隔开，这就与语句的结束符号“；”想冲突了。因此为了解决该问题，可以使用DELIMITER 语句，改变结束符。例如 “ DELIMITER$ ” ，可以用来实现将结束符号设置成“$$”

【实例】
1、创建触发器----包含多条执行语句
在数据库 company 中存在两个表对象：部门表（t_dept）和日记表（t_diary）,创建触发器实现：当向部门表中插入记录时，就会在 插入之后 向日记表中插入两条记录。

mysql>  #创建触发器#
mysql> DELIMITER $
mysql> CREATE TRIGGER tri_diarytime2
    ->  AFTER INSERT
    ->          ON t_dept FOR EACH ROW
    ->                  BEGIN
    ->                          INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                        END
    ->                  $
Query OK, 0 rows affected (0.12 sec)
mysql> DELIMITER ;

在上述的语句中，首先通过“DELIMITER $ ” 语句设置结束符号为“$”，然后在关键字 BEGIN 和 END 之间编写了执行语句列表，最后通过 “DELIMETER ;” 语句将结束符号还原成默认的结束符号 “；”。
2、验证
验证之前，首先我们先查看一下当前数据库中有几个触发器（验证语句在后面介绍）

mysql>#查看触发器#
mysql> SHOW TRIGGERS \G
*************************** 1. row ***************************
             Trigger: tri_diarytime
               Event: INSERT
               Table: t_dept
           Statement: insert into t_diary values(null,'t_dept',now())
              Timing: BEFORE
             Created: 2022-08-20 14:42:41.94
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: tri_diarytime2
               Event: INSERT
               Table: t_dept
           Statement: BEGIN
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                        END
              Timing: AFTER
             Created: 2023-02-18 16:34:12.05
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
2 rows in set (0.00 sec)

从上面的结果中可以看到目前在本数据库中有两个触发器，一个是有一条语句的触发器，另一个是有两条语句的触发器。为了在验证的过程中不被第一个干扰，我们需要删除第一个触发器。

mysql>#删除触发器#
mysql> DROP TRIGGER tri_diarytime;
Query OK, 0 rows affected (0.02 sec)
mysql>#查看触发器#
mysql> SHOW TRIGGERS \G
*************************** 1. row ***************************
             Trigger: tri_diarytime2
               Event: INSERT
               Table: t_dept
           Statement: BEGIN
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                        END
              Timing: AFTER
             Created: 2023-02-18 16:34:12.05
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.01 sec)

这样，我们就可以向表 t_dept 中插入一条记录，然后查看表 t_diary 中是否执行插入2条当前时间操作。

mysql>#插入记录#
mysql> INSERT INTO t_dept VALUES(2,'cjgongdept','ShangXi');
Query OK, 1 row affected (0.00 sec)

mysql>#查看记录#
mysql> SELECT * FROM t_dept;
+--------+------------+---------+
| deptno | dname      | loc     |
+--------+------------+---------+
|      1 | cjgongdept | ShangXi |
|      2 | cjgongdept | ShangXi |
+--------+------------+---------+
2 rows in set (0.00 sec)

mysql>#查看记录#
mysql> SELECT * FROM t_diary;
+---------+-----------+---------------------+
| diaryno | tablename | diarytime           |
+---------+-----------+---------------------+
|       1 | t_dept    | 2022-09-03 22:13:43|
|       2 | t_dept    | 2023-02-18 16:47:47 |
|       3 | t_dept    | 2023-02-18 16:47:47 |
+---------+-----------+---------------------+
3 rows in set (0.00 sec)

通过插入指令，我们可以看到 t_dept 表中增加了一条记录，随后在 t_diary 表中同时增加了两个记录，这说明我们 tri_diarytime2 触发器创建成功。

三、查看触发器

在创建触发器时如果出现 “Trigger already exists”时，说明你所创建的触发器已经存在了，另外 MySQL 对于具有相同触发程序动作时机（Timing）和事件（Event）的触发器是不允许的。因此，在创建触发器前，应该查看本数据库中已经存在的触发器。

通过 SHOW TRIGGERS 语句查看触发器

语法形式

SHOW TRIGGERS \G

【实例】

mysql>#查看触发器#
mysql> SHOW TRIGGERS \G
*************************** 1. row ***************************
             Trigger: tri_diarytime2
               Event: INSERT
               Table: t_dept
           Statement: BEGIN
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                        END
              Timing: AFTER
             Created: 2023-02-18 16:34:12.05
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

单词	注释
Trigger	触发器的名称
Event	触发器的激活事件
Table	触发器对象触发事件所操作的表
Statement	触发器激活时所执行的语句
Timing	触发器被激活的时机

在上面的结果中我们能看到触发器的名称、激活事件、触发事件所操作的表、触发器激活时所执行的语句以及触发器被激活的时机。

通过查看系统表 triggers 实现查看触发器

在 MySQL 软件中，系统数据库 information_schema 中存在一个存储所有触发器信息的系统表 triggers，因此查询该表格的记录也可以实现查看触发器功能。

mysql>#选择数据库#
mysql> USE information_schema;
Database changed
mysql> SELECT * FROM triggers \G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_insert_set_user
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
    IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
        SET NEW.set_by = USER();
    END IF;
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2022-03-15 21:30:46.94
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DA
TE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: sys
              TRIGGER_NAME: sys_config_update_set_user
        EVENT_MANIPULATION: UPDATE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: sys
        EVENT_OBJECT_TABLE: sys_config
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
    IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
        SET NEW.set_by = USER();
    END IF;
END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2022-03-15 21:30:46.94
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DA
TE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: mysql.sys@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
*************************** 3. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: company
              TRIGGER_NAME: tri_diarytime2
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: company
        EVENT_OBJECT_TABLE: t_dept
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                                INSERT INTO t_diary VALUES(NULL,'t_dept',now());
                        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: 2023-02-18 16:34:12.05
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: gbk
      COLLATION_CONNECTION: gbk_chinese_ci
        DATABASE_COLLATION: utf8_general_ci
3 rows in set (0.00 sec)

从上面的查询结果可以看出来，在 MySQL 中存在3个触发器，前两个为系统自带的，最后一个是我们建立的有两个执行语句的触发器。

四、删除触发器

语法

DROP TRIGGER trigger_name

trigger_name------所要删除的触发器名称

生词表

单词	读音	译文	MySQL
Trigger	英 [ˈtrɪɡə]	vt.触发;引起;发动;开动;起动 n.触发器;(枪的)扳机;(尤指引发不良反应或发展的)起因，诱因;引爆器	触发器
Event	英 [ɪˈvent]	n.事件;发生的事情;	事件
ROW	英[rəʊ , raʊ]	n.一行; 一排; 一列;	记录
DELIMITER	英 [diːˈlɪmɪtə]	n.定界符	设置结束符
Drop	英[drɒp]	n. 滴; 下降; 下跌; v. (意外地)落下，掉下，使落下;	删除