MySQL——触发器

最新推荐文章于 2021-11-12 10:03:08 发布

时光·漫步zth

最新推荐文章于 2021-11-12 10:03:08 发布

阅读量267

点赞数 1

分类专栏： MySQL 文章标签： MySQL触发器创建触发器删除触发器查看触发器

本文链接：https://blog.csdn.net/qq_41573234/article/details/80417247

版权

MySQL 专栏收录该内容

43 篇文章 14 订阅

订阅专栏

一. 创建触发器

触发器 ( trigger )是个特殊的存储过程，不同的是，执行存储过程要用 CALL 语句来调用，而触发器的执行不需要用 CALL语句调用，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被 MySQL 自动调用。比如当对 fruits 表进行INSERT、DELETE 或 UPDATE 操作时就会激活它。

触发器可以查询数据表，而且可以包含复杂的 SQL 语句，主要用于满足复杂的业务规则或要求。例如，可以根据客户当前的账户状态，控制是否允许插入新的订单。

1. 创建只有一个执行语句的触发器

语法规则：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

trigger_name：自定义触发器的名称
trigger_time：触发时机，可以指定为 before 或 after
trigger_event：触发事件，包括 INSERT、UPDATE 和 DELETE
tbl_name：建立触发器的表名，即在哪张表上建立触发器
trigger_stmt：触发器程序体，触发器程序可以使用 begin 和 end 作为开始和结束，中间包含多条语句。

【例】创建一个单执行语句的触发器。

首先创建一个 account 表

mysql> CREATE table account   (  acct_num   int , amount  DECIMAL( 10,2));
Query OK, 0 rows affected (1.42 sec)

创建触发器

mysql> CREATE  TRIGGER   ins_sum  BEFORE  INSERT  ON  account
    -> FOR  EACH  ROW  SET  @sum = @sum + NEW.amount;

查看执行结果：

mysql> SET  @sum =0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT  INTO  account  VALUES  (1,1.00),(2,2.00);
Query OK, 2 rows affected (0.20 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT  @sum;
+------+
| @sum |
+------+
| 3.00 |
+------+
1 row in set (0.00 sec)

触发器的作用是向数据表 acount 插入数据之前，对新插入的 amount 字段值进行求和计算。

2. 创建有多个执行语句的触发器

语法格式如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

trigger_name：自定义触发器的名称
trigger_time：触发时机，可以指定为 before 或 after
trigger_event：触发事件，包括 INSERT、UPDATE 和 DELETE
tbl_name：建立触发器的表名，即在哪张表上建立触发器
trigger_stmt：触发器程序体，触发器程序可以使用 begin 和 end 作为开始和结束，中间包含多条语句。

【例】创建一个包含多个执行语句的触发器

首先创建数据表：

mysql> CREATE  TABLE  test1  (  a1  INT  );
Query OK, 0 rows affected (0.81 sec)

mysql> CREATE  TABLE  test2(a2  INT  );
Query OK, 0 rows affected (0.27 sec)

mysql> CREATE TABLE  test3(  a3  INT  not  null  AUTO_INCREMENT  PRIMARY  KEY );
Query OK, 0 rows affected (0.32 sec)

mysql> CREATE  TABLE  test4 (
    -> a4  INT  NOT  NULL  AUTO_INCREMENT  PRIMARY  KEY,
    -> b4  INT DEFAULT  0 );
Query OK, 0 rows affected (0.33 sec)

创建触发器：

mysql> DELIMITER  //
mysql> CREATE  TRIGGER  testref  BEFORE  INSERT  ON  test1
    -> FOR  EACH  ROW  BEGIN
    -> INSERT  INTO  test2  SET  a2 = NEW.a1;
    -> DELETE  FROM  test3  WHERE  a3 = NEW.a1 ;
    -> UPDATE  test4  SET  b4 = b4 +1  WHERE  a4 = NEW.a1;
    -> END//
Query OK, 0 rows affected (1.04 sec)

mysql> DELIMITER ;

插入数据：

mysql> INSERT  INTO  test3 (a3)  values
    -> (NULL), (NULL),(NULL),(NULL),(NULL),
    -> (NULL),(NULL),(NULL),(NULL),(NULL);
Query OK, 10 rows affected (0.15 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> INSERT  INTO  test4 (a4)  VALUES
    -> (0),   (0), (0), (0), (0),
    ->  (0), (0), (0), (0), (0);
Query OK, 10 rows affected (0.08 sec)
Records: 10  Duplicates: 0  Warnings: 0

查看只想结果：

mysql> INSERT  INTO  test1  VALUES
    -> (1),(3),(1),(7),(1),(8),(4),(4);
Query OK, 8 rows affected (0.65 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT  *  FROM test1;
+------+
| a1   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT  *  FROM test2;
+------+
| a2   |
+------+
|    1 |
|    3 |
|    1 |
|    7 |
|    1 |
|    8 |
|    4 |
|    4 |
+------+
8 rows in set (0.00 sec)

mysql> SELECT  *  FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set (0.00 sec)

mysql> SELECT  *  FROM test4;
+----+------+
| a4 | b4   |
+----+------+
|  1 |    3 |
|  2 |    0 |
|  3 |    1 |
|  4 |    2 |
|  5 |    0 |
|  6 |    0 |
|  7 |    1 |
|  8 |    1 |
|  9 |    0 |
| 10 |    0 |
+----+------+
10 rows in set (0.00 sec)

二. 查看触发器

1. 用 SHOW TRIGGERS 语句查看触发器信息

语法格式如下：

SHOW TRIGGERS；

【例】

mysql> SHOW  TRIGGERS  \G
*************************** 1. row ***************************
             Trigger: ins_sum
               Event: INSERT
               Table: account
           Statement: SET  @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2018-05-23 11:34:28.96
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
*************************** 2. row ***************************
             Trigger: testref
               Event: INSERT
               Table: test1
           Statement: BEGIN
INSERT  INTO  test2  SET  a2 = NEW.a1;
DELETE  FROM  test3  WHERE  a3 = NEW.a1 ;
UPDATE  test4  SET  b4 = b4 +1  WHERE  a4 = NEW.a1;
END
              Timing: BEFORE
             Created: 2018-05-23 21:21:36.02
            sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
2 rows in set (0.01 sec)

2. 在 triggers 表中查看触发器信息

在 MySQL 中，所有触发器的定义都存在于 INFORMATION_SCHEMA 数据库的 TRIGGERS 表中，可以通过查询命令 SELECT 来查看，语法格式如下：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;

【例】通过 WHERE 来指定查看特定名称的触发器。

mysql> SELECT  *  FROM  INFORMATION_SCHEMA.TRIGGERS  WHERE  TRIGGER_NAME = 'testref' \G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test_db
              TRIGGER_NAME: testref
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test_db
        EVENT_OBJECT_TABLE: test1
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN
INSERT  INTO  test2  SET  a2 = NEW.a1;
DELETE  FROM  test3  WHERE  a3 = NEW.a1 ;
UPDATE  test4  SET  b4 = b4 +1  WHERE  a4 = NEW.a1;
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: 2018-05-23 21:21:36.02
                  SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8
      COLLATION_CONNECTION: utf8_general_ci
        DATABASE_COLLATION: utf8_general_ci
1 row in set (0.46 sec)

TRIGGER_SCHEMA 表示触发器所在的数据库；TRIGGER_NAME 的后面是触发器的名称； EVENT_OBJECT_TABLE 表示在哪个数据表上触发； ACTION_STATEMENT 表示触发器触发时执行的具体操作; ACTION_ORIENTATION 的值为 ROW，表示在每条记录上都触发； ACTION_TIMING 表示触发的时刻是 AFTER; 其余的是和系统相关的信息。

也可以不指定触发器名称，这样将查看所有的触发器，命令如下：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS \G

三. 触发器的使用

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。某些触发程序可用于检查插入到表中的值，或对更新涉及的值进行计算。
触发程序与表相关，当对表执行 INSERT、DELETE 或 UPDATE 操作时，将激活触发程序，可以将触发程序设置为在执行语句之前或之后激活。例如，可以在从表中删除每一行之前，或在更新每行之后激活触发程序。

如上面示例。

四. 删除触发器

语法格式如下：

DROP TRIGGER [ schema_name. ] trigger_name

【例】删除数据库 testref。

mysql> DROP TRIGGER  test_db.testref;
Query OK, 0 rows affected (0.04 sec)

查看删除结果：

mysql> SELECT  *  FROM  INFORMATION_SCHEMA.TRIGGERS  WHERE  TRIGGER_NAME = 'testref' \G
Empty set (0.08 sec)

【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版

时光·漫步zth

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录