数据库组成 触发器

举报
xcc-2022 发表于 2022/07/26 10:45:30 2022/07/26
【摘要】 17.1　创建触发器MySQL中创建触发器可以使用CREATE TRIGGER语句。MySQL中的触发器可以包含一条执行语句，也可以包含多条执行语句。17.1.1　语法格式创建触发器的语法格式如下：CREATE [DEFINER = user] TRIGGER trigger_name trigger_time trigger_event ON tbl...

17.1　创建触发器

MySQL中创建触发器可以使用CREATE TRIGGER语句。MySQL中的触发器可以包含一条执行语句，也可以包含多条执行语句。


17.1.1　语法格式

创建触发器的语法格式如下：

CREATE
      [DEFINER = user]
      TRIGGER trigger_name
      trigger_time trigger_event
      ON tbl_name FOR EACH ROW
      [trigger_order]
      trigger_body

语法格式说明如下：

·trigger_name：创建的触发器的名称。

·trigger_time：标识什么时候执行触发器，支持两个选项，分别为BEFORE和AFTER。其中，BEFORE表示在某个事件之前触发，AFTER表示在某个事件之后触发。

·trigger_event：触发的事件，支持INSERT、UPDATE和DELETE操作。

·tbl_name：数据表名称，表示在哪张数据表上创建触发器。

·trigger_body：触发器中执行的SQL语句，可以有一条SQL语句，也可以是多条SQL语句。


17.1.2　创建触发器示例

触发器可以在某个事件发生之前触发，也可以在某个事件发生之后触发。

1．创建测试数据表

创建数据表test_trigger和test_trigger_log。

mysql> CREATE TABLE test_trigger (
      -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      -> t_note VARCHAR(30)
      -> );
  Query OK, 0 rows affected (0.12 sec)
  mysql> CREATE TABLE test_trigger_log (
      -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      -> t_log VARCHAR(30)
      -> );
  Query OK, 0 rows affected (0.01 sec)

SQL语句执行成功，接下来对test_trigger数据表进行增加、删除和修改操作，使用触发器将test_trigger数据表中的数据变化日志写入test_trigger_log数据表中。

2．BEFORE INSERT触发器

例如，创建名称为before_insert的触发器，向test_trigger数据表插入数据之前，向test_trigger_log数据表中插入before_insert的日志信息。

mysql> DELIMITER $$
  mysql> CREATE TRIGGER before_insert
      -> BEFORE INSERT ON test_trigger
      -> FOR EACH ROW
      -> INSERT INTO test_trigger_log 
      -> (t_log)
      -> VALUES
      -> ('before_insert')
      -> ;
      -> $$
  Query OK, 0 rows affected (0.16 sec)
  mysql> DELIMITER ;

SQL语句执行成功，向test_trigger数据表中插入数据。

mysql> INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');
  Query OK, 1 row affected (0.00 sec)

SQL语句执行成功，查看test_trigger数据表中的数据。

mysql> SELECT * FROM test_trigger;
  +----+--------------------------------+
  | id | t_note                         |
  +----+--------------------------------+
  |  1 | 测试 BEFORE INSERT 触发器      |
  +----+--------------------------------+
  1 row in set (0.01 sec)

向test_trigger数据表中成功插入了一条数据。

查看test_trigger_log数据表中的数据。

mysql> SELECT * FROM test_trigger_log;
  +----+---------------+
  | id | t_log         |
  +----+---------------+
  |  1 | before_insert |
  +----+---------------+
  1 row in set (0.00 sec)

向test_trigger数据表插入数据之前，执行触发器向test_trigger_log数据表中插入了before_insert信息。

3．创建AFTER INSERT触发器

例如，创建名称为after_insert的触发器，向test_trigger数据表插入数据之后，向test_trigger_log数据表中插入after_insert的日志信息。

mysql> DELIMITER $$
  mysql> CREATE TRIGGER after_insert
      -> AFTER INSERT ON test_trigger
      -> FOR EACH ROW
      -> BEGIN
      -> INSERT INTO test_trigger_log 
      -> (t_log)
      -> VALUES
      -> ('after_insert');
      -> END;
      -> $$
  Query OK, 0 rows affected (0.00 sec)
  mysql> DELIMITER ;

向test_trigger数据表中插入数据。

mysql> INSERT INTO test_trigger (t_note) VALUES ('测试 AFTER INSERT 触发器');
  Query OK, 1 row affected (0.00 sec)

SQL语句执行成功，查看test_trigger数据表中的数据。

mysql> SELECT * FROM test_trigger;
  +----+--------------------------------+
  | id | t_note                         |
  +----+--------------------------------+
  |  1 | 测试 BEFORE INSERT 触发器      |
  |  2 | 测试 AFTER INSERT 触发器       |
  +----+--------------------------------+
  2 rows in set (0.00 sec)

查看test_trigger_log数据表中的数据。

mysql> SELECT * FROM test_trigger_log;
  +----+---------------+
  | id | t_log         |
  +----+---------------+
  |  1 | before_insert |
  |  2 | before_insert |
  |  3 | after_insert  |
  +----+---------------+
  3 rows in set (0.00 sec)

test_trigger_log数据表中插入了一条before_insert信息和一条after_insert信息，这是因为此时的数据库中存在before_insert触发器和after_insert触发器，当向test_trigger数据表中插入数据时，会先执行before_insert触发器向test_trigger_log数据表中插入数据，再执行after_insert触发器向test_trigger_log数据表中插入数据，所以会向test_trigger_log数据表中插入两条数据。

注意：BEFORE/AFTER UPDATE触发器和BEFORE/AFTER DELETE触发器的创建方式与BEFORE/AFTER INSERT触发器的创建方式相同，此处不再赘述。

17.2　查看触发器

MySQL中支持使用SHOW TRIGGERS和SHOW CREATE TRIGGER语句查看触发器的信息。同时，在MySQL中会将触发器的信息存储在information_schema数据库中的triggers数据表中，所以也可以在trigger数据表中查看触发器的信息。


17.2.1　使用SHOW TRIGGERS语句查看触发器的信息

使用SHOW TRIGGERS语句查看触发器的信息，语法格式如下：

SHOW TRIGGERS
      [{FROM | IN} db_name]
      [LIKE 'pattern' | WHERE expr]

·SHOW TRIGGERS：查看触发器的信息的SQL关键字。

·[{FROM | IN} db_name]：{FROM | IN}表示从哪个数据库中查看触发器。db_name表示数据库名称；此项可以省略，当省略时，查看的是当前MySQL命令行所在的数据库的触发器信息。

·[LIKE 'pattern' | WHERE expr]：查看触发器时匹配的条件语句。

例如，查看当前MySQL命令行所在数据库下的触发器信息。

mysql> SHOW TRIGGERS \G
  *************************** 1. row ***************************
               Trigger: before_insert
                 Event: INSERT
                 Table: test_trigger
             Statement: INSERT INTO test_trigger_log
  (t_log)
  VALUES
  ('before_insert')
                Timing: BEFORE
               Created: 2019-12-26 22:30:44.31
              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@localhost
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_0900_ai_ci
  *************************** 2. row ***************************
               Trigger: after_insert
                 Event: INSERT
                 Table: test_trigger
             Statement: BEGIN
  INSERT INTO test_trigger_log 
  (t_log)
  VALUES
  ('after_insert');
  END
                Timing: AFTER
               Created: 2019-12-26 22:41:57.51
              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@localhost
  character_set_client: utf8mb4
  collation_connection: utf8mb4_general_ci
    Database Collation: utf8mb4_0900_ai_ci
  
  ################此处省略n行代码##########################
  
  6 rows in set (0.00 sec)

结果显示出了MySQL命令行当前数据库下的所有触发器信息，并显示了每个触发器的名称、事件、触发器所在的数据表和触发器执行时触发的SQL语句等信息。


17.2.2　使用SHOW CREATE TRIGGER语句查看触发器的信息

使用SHOW CREATE TRIGGER语句查看触发器的信息，语法格式如下：

SHOW CREATE TRIGGER trigger_name

其中，trigger_name表示触发器的名称。

例如，查看名称为before_insert触发器的信息。

mysql> SHOW CREATE TRIGGER before_insert \G
  *************************** 1. row ***************************
                 Trigger: before_insert
                sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_
  FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER `before_insert` BEFORE INSERT ON 
  `test_trigger` FOR EACH ROW INSERT INTO test_trigger_log
  (t_log)
  VALUES
  ('before_insert')
    character_set_client: utf8mb4
    collation_connection: utf8mb4_general_ci
      Database Collation: utf8mb4_0900_ai_ci
                 Created: 2019-12-26 22:30:44.31
  1 row in set (0.44 sec)

查看结果中显示了触发器的名称、执行触发器时触发的SQL语句。


17.2.3　通过查看triggers数据表中的数据查看触发器的信息

在MySQL中，会将触发器的信息存储到information_schema数据库中的triggers数据表中。可以通过查看information_schema数据库中triggers数据表中的数据来查看触发器的信息。

语法格式如下：

SELECT * FROM information_schema.triggers
  WHERE condition

例如，查看名称为before_insert的触发器信息。

mysql> SELECT * FROM information_schema.triggers WHERE trigger_name = 'before_insert' \G
  *************************** 1. row ***************************
             TRIGGER_CATALOG: def
              TRIGGER_SCHEMA: goods
                TRIGGER_NAME: before_insert
          EVENT_MANIPULATION: INSERT
        EVENT_OBJECT_CATALOG: def
         EVENT_OBJECT_SCHEMA: goods
          EVENT_OBJECT_TABLE: test_trigger
                ACTION_ORDER: 1
            ACTION_CONDITION: NULL
            ACTION_STATEMENT: INSERT INTO test_trigger_log
  (t_log)
  VALUES
  ('before_insert')
          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: 2019-12-26 22:30:44.31
                    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@localhost
        CHARACTER_SET_CLIENT: utf8mb4
        COLLATION_CONNECTION: utf8mb4_general_ci
          DATABASE_COLLATION: utf8mb4_0900_ai_ci
  1 row in set (0.00 sec)

查看结果中显示了before_insert触发器所在的数据库、数据表、触发器名称、事件类型和执行触发器时触发的SQL语句等信息。

当不指定条件时，会查询所有的触发器信息。

mysql> SELECT * FROM information_schema.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: 2019-11-24 12:46:55.84
                    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: mysql.sys@localhost
        CHARACTER_SET_CLIENT: utf8mb4
        COLLATION_CONNECTION: utf8mb4_0900_ai_ci
          DATABASE_COLLATION: utf8mb4_0900_ai_ci
  #############此处省略n行数据#######################
  *************************** 8. row ***************************
             TRIGGER_CATALOG: def
              TRIGGER_SCHEMA: goods
                TRIGGER_NAME: after_delete
          EVENT_MANIPULATION: DELETE
        EVENT_OBJECT_CATALOG: def
         EVENT_OBJECT_SCHEMA: goods
          EVENT_OBJECT_TABLE: test_trigger
                ACTION_ORDER: 1
            ACTION_CONDITION: NULL
            ACTION_STATEMENT: BEGIN
  INSERT INTO test_trigger_log
  (t_log)
  VALUES
  ('after_delete')
  ;
  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: 2019-12-26 23:35:19.34
                    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@localhost
        CHARACTER_SET_CLIENT: utf8mb4
        COLLATION_CONNECTION: utf8mb4_general_ci
          DATABASE_COLLATION: utf8mb4_0900_ai_ci
  8 rows in set (0.00 sec)

17.3　删除触发器

当在数据库中确认某个触发器不再使用时，就可以将不再使用的触发器删除，在MySQL中，使用DROP TRIGGER语句删除触发器。


17.3.1　语法格式

删除触发器的语法格式如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

语法格式说明如下：

·DROP TRIGGER：删除触发器的SQL关键字。

·schema_name：触发器所在的数据库名称，可以省略。当省略时，会删除MySQL命令行所在的数据库下的触发器。

·trigger_name：触发器的名称。


17.3.2　删除触发器示例

例如，删除名称为after_delete的触发器。

mysql> DROP TRIGGER after_delete;
  Query OK, 0 rows affected (0.11 sec)

SQL语句执行成功，查看alter_delete触发器的信息。

mysql> SHOW CREATE TRIGGER after_delete \G
  ERROR 1360 (HY000): Trigger does not exist

结果显示，MySQL报错，不存在alter_delete触发器，说明alter_delete触发器已经被成功删除。

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入