命令行操作MySQL - 触发器 的用法

这是命令行操作MySQL数据库系列博客的第二十三篇，今天这篇博客记录数据库的触发器详细操作。触发器相当于“事件”，当这个“事件”被触发时，与其关联的代码会相应执行！

---

一、触发器的概念精讲

在实际开发中往往会碰到这样的情况：
当我们对一个表进行数据操作时，需要同步对其它的表执行相应的操作，正常情况下，如果我们使用sql语句进行更新，将需要执行多条操作语句！

比如，在某些棋牌游戏中，当玩家充值金币后，玩家表数据库中金币增加的同时，玩家所属的代理会得到相应的提成并计入代理的收益中，即代理数据库表提成记录字段也要同步更新。

而以上的场景，我们可以轻松使用触发器来实现！

就简单来说：
一张表进行插入数据、更新数据、删除数据时，会间接的触发一些“操作”，而这个操作是我们指定的操作，例如对A表进行插入数据，然后B表对A表进行数据个数统计自增，也就是A表每插入一条数据，B表中的一个字段就会自增一，与A表数据个数保持相同。
这就是触发器的作用！！

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。MySQL从5.0.2版本开始支持触发器。

通过本博客的学习，我们将了解触发器的含义和作用、如何创建触发器、查看触发器和删除触发器的方法。同时，可以了解各种事件的触发器的执行情况。

为下面例子做准备：

# 创建班级表
create table class(id int AUTO_INCREMENT PRIMARY KEY, class_name varchar(128), teacher varchar(128), count int);
# 插入数据
insert into class values(101, 'C语言班', '红老师', 0), (102, 'C++班', '绿老师', 0), (103, 'qt班', '蓝老师', 0);

# 创建student表
create table student(id int AUTO_INCREMENT PRIMARY KEY, student_name varchar(128), sex enum('M', 'F'), class_id int);
# student表不需要插入数据

---

二、创建触发器

1. 触发器包含单条执行语句

在MySQL中创建触发器通过SQL语句CREATE TRIGGER来实现，其语法形式如下：
create TRIGGER 触发器名 BEFROE | AFTER (INSERT | UPDATE | DELETE) ON 表名 FOR EACH ROW 触发器触发的语句;

BEFROE | AFTER ：在…之前 | 在…之后；这两个参数选其中一个；（在插入之前，在插入之后；在删除之前，在删除之后；在更新之前，在更新之后）
(INSERT | UPDATE | DELETE)：插入 | 更新 | 删除；这三个参数选其中一个；
触发器触发的执行语句：意思是需要触发器触发什么样的操作。

执行语句中如果要引用更新记录中的字段，对于INSERT语句,只有 NEW 是合法的，表示当前已插入的记录；对于DELETE语句，只有 OLD 才合法，表示当前删除的记录；而UPDATE语句可以和NEW（更新后）以及OLD（更新前）同时使用。

上面这句话可能比较难理解，没关系，看下面例子时，就会知道了！

注意：不能创建具有相同名字的触发器。另外，对于具有相同触发程序动作时间和事件的给定表，不能有两个触发器。因此，对于有经验的用户，在创建触发器之前，需要查看MySQL中是否已经存在该标识符的触发器和触发器的相关事件。

创建触发器名称建议以“tri_”开头，方便区分！

(1). INSERT

例一：
创建触发器，向学生表插入一条记录时，对应class表id的记录字段count自增一。

create TRIGGER tri_class_count AFTER INSERT ON student FOR EACH ROW update class set count = count + 1 where class.id = NEW.class_id;

触发器触发的执行语句：
update class set count = count + 1 where class.id = NEW.class_id;

这条SQL语句中，使用到了NEW关键字，这里的NEW关键字表示受影响的行；因为是新插入的关系，所以得使用NEW。

例二：
现在触发器创建好，往student表插入一条数据看看

insert into student values(1, '小黄', 'M', 101);

查看student表与class表

student表中成功插入一条数据后，对应class表id相同的记录count字段也进行了自增一操作。

例三：
连续插入多条记录

insert into student values(2, '小白', 'M', 103), (3, '小紫', 'F', 102), (4, '小黑', 'M', 103);

也是一样可以触发！

(2). DELETE

例一：
创建触发器，向学生表删除一条记录时，对应class表id的记录字段count自减一。

create TRIGGER tri_student_del AFTER DELETE ON student FOR EACH ROW update class set count = count - 1 where class.id = OLD.class_id;

触发器触发的执行语句：
update class set count = count - 1 where class.id = OLD.class_id

注意：这里是删除操作，删除就是将旧的数据删掉，所以这里得使用OLD。

例二：
删除一条数据

delete from student where id = 1;

当我们删除student表中的一条记录时，class表中的count字段也会减一。

(3). UPDATE

用法和上面INSERT 和 DELETE 一样，唯一需要注意的是：
当你需要使用更新前的字段记录，就得使用OLD；使用更新后的字段记录，就得使用NEW。

2. 触发器包含多条执行语句

创建语句：

DELIMITER &&
create TRIGGER 触发器名 BEFROE | AFTER (INSERT | UPDATE | DELETE) ON 表名 FOR EACH ROW 
BEGIN
触发器触发的语句1;
触发器触发的语句2;
......
END;
&&
DELIMITER ;

触发器包含多条执行语句, 语法多出来两个关键字BEGIN和END，在这两个关键字之间是所要执行的多个执行语句的内容，执行语句之间用分号隔开。

在MySQL中，一般情况下用 “ ; ” 符号作为语句的结束符号，可是在创建触发器时，需要用到 “ ; ” 符号作为执行语句的结束符号。为了解决该问题，可以使用关键字DELIMITER语句。
例如，“DELIMITER $ $ ”可以将结束符号设置成“$$”。
当创建完成后，必须将“;”设置回来，否则后面还会一直使用 $ $作为结束符。

在写例子之前，先创建grade表，作为辅助：

create table grade(id int PRIMARY KEY, math tinyint unsigned, chinese tinyint unsigned, english tinyint unsigned);

insert into grade values(2, 72, 64, 89), (3, 54, 69, 87), (4, 78, 79, 89);

查看student表和class表现状：

例：
创建触发器，当删除student表一条记录时，class表count字段对应减一，grade表对应删除一条记录。

这里我将$$写成&&，区别不大，一样可以使用，建议写成 $ $

DELIMITER &&
create TRIGGER tri_delete_student AFTER DELETE ON student FOR EACH ROW
BEGIN
delete from grade where id = OLD.id;
update class set count = count - 1 where id = OLD.class_id;
END;
&&
DELIMITER ;

例：
删除一条记录，查看三张表的变化

 delete from student where id = 4;

当student表的一条记录删除后，会触发class表更新count字段，会触发grade表删除对应记录。

---

三、查看触发器

show triggers;

通过该语句，可以查找出创建的触发器，虽然有点凌乱，但是也还是可以看的出来。

我们通过名称中“tri_”开头就可以识别出来了。

---

四、删除触发器

drop trigger 触发器名;

---

五、总结

在实际开发中，触发器一般由高级程序员或者专门的数据库设计师进行设计，所以我们只需了解触发器的概念，简单的使用即可；后期如果自己成为了高级程序员，再去看一下文档，也很快就能上手触发器的高级用法了。