资讯

历史

科技

环境与自然

成长

游戏

财经

文学与艺术

美食

健康

家居

文化

情感

汽车

三农

军事

旅行

运动

教育

生活

星座命理

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

创作时间:

作者:

@小白创作中心

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

引用

CSDN

https://blog.csdn.net/cpp_learner/article/details/115703416

触发器是MySQL数据库中一个非常重要的概念，它允许在特定事件（如插入、更新或删除数据）发生时自动执行预定义的操作。本文将详细介绍触发器的概念、创建方法、查看和删除操作，帮助读者全面掌握这一数据库管理工具。

一、触发器的概念精讲

在实际开发中，我们经常会遇到这样的场景：当对一个表进行数据操作时，需要同步对其它表执行相应的操作。例如，在某些棋牌游戏中，当玩家充值金币后，不仅玩家表中的金币需要增加，玩家所属的代理也需要得到相应的提成并计入代理的收益中。

触发器（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 触发器名 BEFORE | AFTER (INSERT | UPDATE | DELETE) ON 表名 FOR EACH ROW 触发器触发的语句;

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

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

(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 触发器名 BEFORE | 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 ;

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