MySQL触发器

触发器：当一张表发生了某件事（插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句。

一、创建触发器
(1)创建只有一个执行语句的触发器
(2)创建有多个执行语句的触发器
二、查看触发器
(1)show triggers语句查看触发器信息
(2)在triggers表中查看触发器信息

三、使用触发器
四、删除触发器

---

一、创建触发器

(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是触发器执行语句。

【例1】创建一个单执行语句的触发器，SQL语句如下：

mysql> create table account (acct_num int,amount decimal(10,2));
Query OK, 0 rows affected (0.12 sec)

mysql> create trigger ins_sum before insert on account
    -> for each row set @sum = @sum + NEW.amount;
Query OK, 0 rows affected (0.07 sec)

mysql> set @sum = 0;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into account values(1,1.00),(2,2.00);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

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

首先创建一个account表，表中有两个字段acct_num和amount，接着创建一个名为ins_sum的触发器，触发的条件是向数据表account插入数据之前，对新插入的amount字段值进行求和计算。

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

创建多个执行语句的触发器的语法如下：

create trigger trigger_name trigger_time trigger_event
on tbl_name for each row
begin
语句执行列表
end 

• trigger_name标识触发器的名称，用户自行指定
• trigger_time标识触发时机,可以指定为before或after
• trigger_event标识触发事件，包括insert、update和delete
• table_name标识建立触发器的表名，即在哪张表上建立触发器
• 触发器程序可以使用begin和end作为开始和结束，中间包含多条语句

【例2】创建一个包含多个执行语句的触发器，sql语句如下：

mysql> create table test1(a1 int);
Query OK, 0 rows affected (0.10 sec)

mysql> create table test2(a2 int);
Query OK, 0 rows affected (0.10 sec)

mysql> create table test3(a3 int not null auto_increment primary key);
Query OK, 0 rows affected (0.08 sec)

mysql> create table test4(
    -> a4 int not null auto_increment primary key,
    -> b4 int default 0
    -> );
Query OK, 0 rows affected (0.10 sec)

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 (0.07 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.06 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.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

上面的代码创建了一个名为testref的触发器，这个触发器的条件是在向表test1插入数据前执行触发器的语句，具体执行代码如下：

mysql> insert into test1 values
    -> (1),(3),(1),(7),(1),(8),(4),(4);
Query OK, 8 rows affected (0.07 sec)
Records: 8  Duplicates: 0  Warnings: 0

4个表的数据如下:

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)

执行结果显示，在向test1插入记录的时候，test2、test3、test4都发生了变化。从这个例子看insert触发了触发器，向test2中插入了test1中的值，删除了test3中相同的内容，同时更新了test4中的b4，即与插入的值相同的个数。

二、查看触发器

(1)show triggers语句查看触发器信息

【例3】通过show triggers命令查看一个触发器，代码如下：

mysql> create table myevent
    -> (
    -> id int(11) default null,
    -> evt_name char(20) default null
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> create trigger trig_update after update on account
    -> for each row insert into myevent values(1,'after update');
Query OK, 0 rows affected (0.04 sec)

使用show triggers命令查看触发器：

mysql> show triggers \G
*************************** 1. row ***************************
             Trigger: ins_sum
               Event: INSERT
               Table: account
           Statement: set @sum = @sum + NEW.amount
              Timing: BEFORE
             Created: 2019-09-08 13:37:29.40
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
             Trigger: trig_update
               Event: UPDATE
               Table: account
           Statement: insert into myevent values(1,'after update')
              Timing: AFTER
             Created: 2019-09-08 14:27:16.39
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 3. 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: 2019-09-08 14:12:34.15
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
  Database Collation: utf8mb4_0900_ai_ci
3 rows in set (0.00 sec)

• Trigger表示触发器的名称
• Event表示激活触发器的事件
• Table表示激活触发器的操作对象表
• Timing表示触发器触发的时间
• Statement表示触发器执行的操作

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

在MySQL中所有触发器的定义都存在information_schema数据库的triggers表格中，可以通过查询命令select来查看，具体语法如下：

mysql> select * from information_schema.triggers where trigger_name = 'trig_update' \G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: test
              TRIGGER_NAME: trig_update
        EVENT_MANIPULATION: UPDATE
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: test
        EVENT_OBJECT_TABLE: account
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: insert into myevent values(1,'after update')
        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-09-08 14:27:16.39
                  SQL_MODE: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: gbk
      COLLATION_CONNECTION: gbk_chinese_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.03 sec)

• trigger_schema表示触发器 所在的数据库
• trigger_name后面是触发器的名称
• event_object_table表示在哪个数据表上触发
• action_statement表示触发器触发的时候执行的具体操作
• action_orientation是row表示在每条记录上都触发
• ation_timing表示触发的时刻是after，剩下的是和系统相关的信息

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

select * from information_schema.triggers \G

三、使用触发器

触发程序与表相关，当对表执行insert、delete或update语句时，将激活触发程序，可以将触发程序设置为在执行语句之前或之后激活。例如，可以在从表中删除每一行之前或在更新每一行之后激活触发程序。

【例4】创建一个在account表插入记录之后更新myevent数据表的触发器，sql语句如下:

mysql> create trigger trig_insert after insert on account
    -> for each row insert into myevent values(2,'after insert');
Query OK, 0 rows affected (0.02 sec)

mysql> insert into account values (1,1.00),(2,2.00);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from myevent;
+------+--------------+
| id   | evt_name     |
+------+--------------+
|    2 | after insert |
|    2 | after insert |
+------+--------------+
2 rows in set (0.00 sec)

从执行的结果来看，是创建了一个名称为trig_insert的触发器，是在向account插入记录之后进行触发的，执行的操作是向表myevent插入一条记录。

四、删除触发器

使用drop trigger语句可以删除MySQL中已经定义的触发器，删除触发器语法格式如下：

drop trigger [schema_name.] trigger_name

• schema_name表示数据库名称，可选。如果省略了，将会从当前数据库中舍弃触发程序
• trigger_name是要删除的触发器的名称

【例5】删除一个触发器，sql语句如下：

mysql> drop trigger ins_sum;
Query OK, 0 rows affected (0.06 sec)

触发器ins删除成功。