MySQL——触发器
核心知识点：
1.什么是触发器？触发器的优点？触发器的类型？触发器的数量？
2.触发器的操作有哪些？
3.OLD和NEW
一、什么是触发器
触发器是个特殊的存储过程，它的执行不是由程序调用，也不手工启动，而是由操作来触发。
当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。（什么是触发器？）
触发器有一个非常好的特性：触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的修改。什么意思了？就是如果你这个事务执行了一半，程序崩了，它就会回到开始执行的位置，也就是什么都没做。
触发器经常用于加强数据的完整性约束和业务规则。
总的来说，触发器有两个优点：自动化和支持回滚保持数据完整性。
二、触发器的创建
语法：
DELIMITER // 修改结束符
CREATE TRIGGER 触发器名称 触发时机 触发类型 ON 被监控表名 FOR EACH ROW
BEGIN
执行动作
END
DELIMITER ;
触发器名称：一般采用“表名_触发器_动作”这种形式
触发时机：BEFORE或AFTER，指明触发程序是在激活它之前或是之后触发
触发类型：insert、update、delete
被监控表名：创建触发器的表名，也就是被监控的那张表
执行动作：一条或者多条SQL语句
由此可见，可以建立6种触发器，即：before insert、before update、before delete、after insert、after update和after delete。
另外，一张表上同一种类型的触发器只能有一个，因此一张表上最多有6个触发器
触发类型详解：
MySQL除了对INSERT、UPDATE、DELETE基本操作进行定义外，还定义了LOAD DATA和REPLACE语句，这两种语句也能引起上述6种类型中的触发器的执行。
LOAD DATA：用于将一个文件装入到一个数据表中，相当于一系列的INSERT操作。
REPLACE：和INSERT很像，只是在表中有primary key或unique索引时，
如果插入的数据和原来的primary key或unique索引一致，会先删除原来的数据，然后增加一条新数据，
也就是说，一条REPLACE语句有时候等价于一条INSERT语句，有时候等价于一条DELETE语句加上一条INSERT语句。
这就是LOAD DATA和REPLACE会触发触发器，因为它们有时候等价于INSERT或DELETE。
下面给出2个例子
例子1
预期效果：往学生信息表中添加信息的时候，会将添加的动作记录到另一张表。
mysql> select * from student_info; +----+--------+---------+--------------+------------+----------+ | id | name | dorm | addr | account | passwd | +----+--------+---------+--------------+------------+----------+ | 2 | 毛线 | 6号楼 | 湖北孝感 | 1041031715 | tyujkljk | | 3 | 黄鱼 | 5号楼 | 湖北罗田 | 1041031732 | 5uy9g6 | | 4 | 子豪 | 6号楼 | 江苏扬州 | 1041031721 | rtyui | | 5 | 星爷 | 5号楼 | 湖北孝感 | 1041031743 | tghj8g | | 6 | 代鹏 | 5号楼 | 江苏盐城 | 1041031701 | gvhui745 | | 7 | 子栋 | 6号楼 | 湖北洪湖 | 1041031723 | 568g29 | | 8 | 周攀 | 6号楼 | 湖北武穴 | 1041031719 | 976uyif | | 9 | 大爷 | 5号楼 | 湖北通城 | 1041031710 | cvbdf | | 10 | 小鸟 | 6号楼 | 湖北襄阳 | 1041031737 | 456dfg | +----+--------+---------+--------------+------------+----------+ 9 rows in set (0.00 sec)
mysql> desc trigger_lst;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| action | varchar(20) | YES | | NULL | |
| time | datetime | YES | | NULL | |
+--------+-------------+------+-----+---------+----------------+
#创建触发器 mysql> delimiter // mysql> create trigger student_info_insert after insert on student_info for each row -> begin -> insert into trigger_lst(action,time) values('insert',(select now())); -> end; -> // Query OK, 0 rows affected (0.01 sec)
mysql> insert into student_info values(20,'joan','8号楼','LD',1041031128,'fghj'); -> // Query OK, 1 row affected (0.00 sec) mysql> select * from trigger_lst; -> // +----+--------+---------------------+ | id | action | time | +----+--------+---------------------+ | 1 | insert | 2017-12-13 00:36:22 | +----+--------+---------------------+ 1 row in set (0.00 sec) #触发成功
例子2
假设有这么两张表
mysql> desc class; +----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+----------+------+-----+---------+-------+ | classID | char(20) | YES | | NULL | | | stucount | int(4) | YES | | NULL | | +----------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
mysql> desc student; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | stuID | int(10) | YES | | NULL | | | classID | char(20) | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
预期效果：创建触发器来使班级表中每个班内的学生随着学生的添加而自动更新
#创建触发器 mysql> create trigger student_insert after insert on student for each row -> begin -> declare c int; #声明参数及类型 -> set c = (select stucount from class where classID = new.classID); #赋值 -> update class set stucount = c + 1 where classID = new.classID; #不用new怎么表示状态
-> end// Query OK, 0 rows affected (0.00 sec)
mysql> insert into student values(5678,'一班'); Query OK, 1 row affected (0.00 sec) mysql> insert into student values(4523,'一班'); Query OK, 1 row affected (0.01 sec) mysql> select * from class; #原来一班只有23个人 +---------+----------+ | classID | stucount | +---------+----------+ | 一班 | 25 | | 二班 | 19 | +---------+----------+ 2 rows in set (0.00 sec)
其实上面这个例子我想说明另一个概念：old和new
在MySQL中，old和new用来表示触发器的所在表中，触发了触发器的那一行数据。
在INSERT型触发器中，NEW用来表示将要（before）或已经（after）插入的新数据；
在UPDATE型触发器中，OLD表示将要或已经被修改的原数据，NEW表示将要或已经修改的新数据；
在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据。
另外，OLD是只读，而NEW则可以在触发器中使用SET赋值，这样不会再此触发触发器，造成循环调用。
三、其他操作
1.触发器的查看
语法：SHOW TRIGGERS [FROM 库名]; #[]表示可选
mysql> show triggers; +---------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +---------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+ | tg1 | INSERT | score | begin insert into tg(time) values(now()); end | AFTER | NULL | | root@localhost | utf8 | utf8_general_ci | utf8_general_ci | | student_insert | INSERT | student | begin declare c int; set c = (select stucount from class where classID = new.classID); update class set stucount = c + 1 where classID = new.classID; end | AFTER | NULL | | root@localhost | utf8 | utf8_general_ci | utf8_general_ci | | student_info_insert | INSERT | student_info | begin insert into trigger_lst(action,time) values('insert',(select now())); end | AFTER | NULL | | root@localhost | utf8 | utf8_general_ci | utf8_general_ci | +---------------------+--------+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------+---------+----------+----------------+----------------------+----------------------+--------------------+ 3 rows in set (0.00 sec)
2.删除触发器
语法：DROP TRIGGER[database.]trigger_name;
mysql> drop trigger student_insert; Query OK, 0 rows affected (0.00 sec) #表示删除成功
