MySQL-5.7 创建及查看触发器

触发器的作用是当表上有对应SQL语句发生时，则触发执行。

1.语法

CREATE
    [DEFINER = { user | CURRENT_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

说明：

• trigger_name：触发器的名称，不能与已经存在的触发器重复；
• trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；
• trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；
• tbl_name：该触发器作用在tbl_name上；

2.关键说明

• Definer关键词用来指定trigger的安全环境；
• trigger_time指定触发器的执行时间，BEFORE和AFTER指定触发器在表中的每行数据修改前或者后执行；
• trigger_event指定触发该触发器的集体事件：
  INSERT 当新的一行数据插入表中时触发，如执行insert，load data，replace语句插入新数据
  UPDATE 当表的一行数据被修改时触发，如执行update语句时
  DELETE 当表的一行数据被删除时触发，如执行delete，replace语句时
• 当执行insert into...on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器；
• 从5.7.2版本开始，可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器，默认情况下，按照创建时间依次执行，通过指定FOLLOWS/PRECEDES改变执行顺序，即FOLLOWS时表示新创建的触发器执行，PRECEDES则表示新触发器先执行；
• trigger_body 表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段，OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示数据被插入或修改之后的字段数据；

3.实例

mysql> delimiter //
mysql> create trigger simple_trigger
    -> after update
    -> on students for each row
    -> begin
    -> insert into students_bak values(old.sid,old.sname,new.sname,old.gender,new.gender,now());
    -> end//
Query OK, 0 rows affected (0.16 sec)

mysql> select * from students;
+-----+--------+--------+---------+
| sid | sname  | gender | dept_id |
+-----+--------+--------+---------+
|   1 | Andrew | -1     |       1 |
|   2 | Andy   | -1     |       1 |
|   3 | Bob    | -1     |       1 |
|   4 | Ruth   | -1     |       2 |
|   5 | Mike   | -1     |       2 |
|   6 | John   | 0      |       3 |
|   7 | Cindy  | 1      |       3 |
|   8 | Susan  | 1      |       3 |
+-----+--------+--------+---------+
8 rows in set (0.01 sec)

mysql> update students set sname='abc',gender=1 where sid=1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from students;
+-----+-------+--------+---------+
| sid | sname | gender | dept_id |
+-----+-------+--------+---------+
|   1 | abc   | 1      |       1 |
|   2 | Andy  | -1     |       1 |
|   3 | Bob   | -1     |       1 |
|   4 | Ruth  | -1     |       2 |
|   5 | Mike  | -1     |       2 |
|   6 | John  | 0      |       3 |
|   7 | Cindy | 1      |       3 |
|   8 | Susan | 1      |       3 |
+-----+-------+--------+---------+
8 rows in set (0.00 sec)

mysql> select * from students_bak;
+--------+----------+----------+-----------+-----------+---------------------+
| oldsid | oldsname | newsname | oldgender | newgender | tstamp              |
+--------+----------+----------+-----------+-----------+---------------------+
|      1 | Andrew   | abc      |        -1 |         1 | 2017-12-13 11:04:52 |
+--------+----------+----------+-----------+-----------+---------------------+
1 row in set (0.00 sec)

4.查看触发器（information_schema）

mysql> select TRIGGER_SCHEMA,TRIGGER_NAME,DEFINER,ACTION_STATEMENT from TRIGGERS where TRIGGER_SCHEMA='course';
+----------------+----------------+----------------+-----------------------------------------------------------------------------------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME   | DEFINER        | ACTION_STATEMENT                                                                                    |
+----------------+----------------+----------------+-----------------------------------------------------------------------------------------------------+
| course         | simple_trigger | root@localhost | begin
insert into students_bak values(old.sid,old.sname,new.sname,old.gender,new.gender,now());
end |
+----------------+----------------+----------------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

5.删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

• if exists用来避免删除不存在的触发器时引发的报错
• 当你执行drop table时，表上的触发器也被drop掉了

mysql> drop trigger simple_trigger;
Query OK, 0 rows affected (0.00 sec)