触发器的作用是当表上有对应SQL语句发生时，则触发执行。
1.语法
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_bodytrigger_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: 0mysql> 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 | begininsert 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)