学习MySQL复杂查询——day03

一、Trigger触发器

1、什么是触发器，是用来做什么的？

（1）触发器是一种与MySQL数据库有关的数据库对象，是对表操作时触发执行的对象。其实是一种特殊的存储过程，它的执行并不是程序调用，也不是人为启动，而是事件触发。
（2）触发器可以用来对一张表进行增、删、改操作的同时，也可以对另外一张表自动化完成操作。比如，我们在学生表中加入一个学生信息，那么学生表所对应的成绩表中的总成绩应该也要加上加入的学生成绩。这样才可以保证表的完整性。

2、触发器的语法

（1）语法说明

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON table_name
FOR EACH ROW
BEGIN
    trigger_stmt
END

trigger_name：触发器名称
trigger_time：触发时间，分为BEFORE、AFTER，表示事件发生前或者后。
trigger_event：触发事件，分为insert、update、delete三种触发（也就是在表执行insert、update、delete操作时触发），除此之外没有。
table_name：表名，哪张表上建立触发器。
FOR EACH ROW：触发器的执行间隔，让触发器每隔一行执行一次，并不是整个表。
trigger_stmt：触发器多要触发的SQL语句，这里没有限制，可以是简单的SQL语句，也可以是复杂的SQL语句。

（2）简单实例
每次向学生成绩表中添加一条记录，就会自动触发向班级总成绩记录表中总分上加新学生的成绩分数。

创建stduents_scroe表：用于记录每位学生的成绩

创建class_score表：用于记录班级总成绩

若是在一班、二班、三班的学生时，会在class_score表中对应的班级上加上分数。
若不是一班、二班、三班的学生时，会自动在class_score表中增加新的班级记录。
具体代码如下：

delimiter //
DROP TRIGGER IF EXISTS update_sum;
CREATE TRIGGER update_sum
AFTER INSERT  ON students_score
FOR EACH ROW
BEGIN
    DECLARE cn varchar(10);
    DECLARE score int;
    SET cn = new.CLASS;
    SET score = new.SCORE;
    IF (cn='一班' or cn='二班' or cn='三班') THEN
        UPDATE class_score SET TOTAL_SCORE=TOTAL_SCORE+score WHERE CNAME=cn;
    ELSE
        INSERT INTO class_score(CNAME,TOTAL_SCORE) VALUES(new.CLASS,new.SCORE);
    END IF;
END
//
delimiter ;

下面向students_score表中插入三条数据：

INSERT INTO students_score VALUES('7','Zhangtao','一班',80);
INSERT INTO students_score VALUES('8','Liutao','二班',82);
INSERT INTO students_score VALUES('9','Wangtao','四班',90);

这时候会在表class_score中增加了总分数，也添加了四班的新纪录。

（3）new/old关键字说明

• 在insert型触发器中，new是表示将要（before）或者插入后（after）的新数据。这里使用old是不合法的。
• 在update型触发器中，old是表示将要（before）或已经被修改的（after）原数据，new是表示update更新之后的数据。这里old、new都可以使用。
• 在delete型触发器中，old是表示将要（before）或已经被删除的（after）原数据。这里使用new是不合法的。

3、触发器的防循环机制

我们设想：我们在对students_score表中加入新的学生记录，在ID前都要加个前缀2017。也就是在触发器中捕捉insert on students_score。之后在触发SQL语句中执行：

DECLARE sID varchar(10);
-- 对插入的学号加2017
set sID=concat('2017',new.ID);
-- 再向触发器绑定的表中插入新的记录，会引起循环
INSERT INTO students_score(ID,SNAME,CLASS,SCORE) VALUES(sID,new.SNAME,new.CLASS,new.SCORE);

再次向students_score表中insert新的记录，会引起循环

那么触发器绑定after update on studens_score呢？

DECLARE sID varchar(10);
-- 对插入的学号加201700
SET sID= concat('201700',new.ID); -- 可以set赋值new
UPDATE students_score SET new.ID=sID;

仍然引起循环

但是以下这样的情况就不会引起触发循环。
在update、delete、insert之前（before）更新set赋值new.column值。而不是update。
也就是old值只能是只读状态，new值可以被set赋值，这样可以避免触发循环

对每个新插入的学生记录，都要在ID前面自动加上201700前缀。

delimiter //
DROP TRIGGER IF EXISTS update_id;
CREATE TRIGGER update_id
BEFORE INSERT  ON students_score
FOR EACH ROW
BEGIN
    SET new.ID = concat('201700',new.ID); -- 可以set赋值new
END
//
delimiter ;

插入以下学生记录

INSERT INTO students_score VALUES('10','Wangbadan','一班',78);

ID自动加上了201700前缀

同样对表students_score进行update更新时，会自动增加201700的ID前缀，但是此时update必须是对ID进行update操作，不然不会有new.ID的值。

delimiter //
DROP TRIGGER IF EXISTS update_id;
CREATE TRIGGER update_id
BEFORE UPDATE  ON students_score
FOR EACH ROW
BEGIN
    SET new.ID = concat('201700',new.ID); -- 可以set赋值new
END
//
delimiter ;

update更新ID：

UPDATE students_score SET ID='8' WHERE SNAME='Litao';

结果为：

对此做出以下的总结：

• 不能在绑定了触发器的insert、update、delete操作中再次使用update、insert、delete，这样会引起循环
• 在update、insert之前（before）可以set赋值new值，做到插入更新前再一次更新插入的记录值。不能在之后after赋值，after之后的的new只能是只读状态
• delete之前不能对old赋值，old只能读。使用new也是不合法的。