目录
7.1 触发器
7.1.1 触发器简介
触发器是一种比较特殊的存储过程,它的执行不是由程序调用,也不是手工调用,而是通过事件来进行触发。
比如说 对一张表 进行(增,删,改),去激活它的执行。
触发器经常应用在加强数据完整性,和业务规则中,如:当一个学生表中添加了一个学生信息时,那么对应的学生数目肯定会有所改变。像这样的情况,我们就可以针对学生表创建一个触发器:以确保每次增加一个学生记录时,就执行一次关于学生总数的计算操作,从而确保学生总数与记录数的一致性。
7.1.2 触发器的创建
#语法要求
CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
触发器程序体;
END
#说明
<触发器名称> 参考索引命名||表命名
BEFORE|AFTER 触发器时机
INSERT DELETE UPDATE 触发的事件(分别是插入、删除、更新)
ON 表名 在哪张表上建立触发器
FOR EACH ROW 触发器执行间隔策略 ===> 字句通知触发器,每隔一行执行一次动作,并不是对整个表执行。
触发器程序体 要出发的SQL语句
7.1.3 示例
#当前有两张表 student student_count
#示例1:每当学生表中有记录增加(减少)时那么学生行数的这个表,需要进行最新的行数统计
1.创建表(建在了myshool_db里)
CREATE TABLE student(
id int auto_increment primary key not null,
name varchar(40)
);
CREATE TABLE student_count(
totle int
);
#添加一条学生信息
INSERT INTO student (name) VALUES('jack_1');
INSERT INTO student_count VALUES(1);
mysql> select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | jack_1 |
+----+--------+
mysql> select * from student_count;
+-------+
| totle |
+-------+
| 1 |
+-------+
这样手动往添加student表里一条学生信息,同样还要手动条件更新student_count表的行数统计,操作太繁琐了,我们可以创建一个触发器,在往student表里添加学生信息后自动更新行数统计。
#而student_count语句全都会变为UPDATE格式
CREATE TRIGGER student_insert_trigger
AFTER INSERT ON student
FOR EACH ROW
BEGIN
UPDATE student_count SET totle = totle+1;
END;
(注意:直接把上面创建触发器的命令不能直接粘贴到xshell的mysql的输入命令行中,这种方法无法执行,会报错,需打开Navicat Premium 15 应用中新建查询输入才可执行。)
#查看触发器是否已存在
SHOW TRIGGERS;
#我们插入若干条数据进行测试
INSERT INTO student (name) VALUES('jack_2'),('jack_3'),('jack_4'),('jack_5'),('jack_6'),('jack_7'),('jack_8'),('jack_9'),('jack_10');
mysql> SELECT * FROM student_count;
+-------+
| totle |
+-------+
| 10 |
+-------+
#套用语法完成删除的触发器
CREATE TRIGGER student_delete_trigger
AFTER DELETE
ON student FOR EACH ROW
BEGIN
UPDATE student_count SET totle = totle-1;
END;
#数据删除数据进行测试:
mysql> SELECT * FROM student_count;
+-------+
| totle |
+-------+
| 10 |
+-------+
mysql> SELECT * FROM student;
+----+---------+
| id | name |
+----+---------+
| 1 | jack_1 |
| 2 | jack_2 |
| 3 | jack_3 |
| 4 | jack_4 |
| 5 | jack_5 |
| 6 | jack_6 |
| 7 | jack_7 |
| 8 | jack_8 |
| 9 | jack_9 |
| 10 | jack_10 |
+----+---------+
mysql> DELETE FROM student WHERE id IN (5,6,7,8,9,10);
mysql> SELECT * FROM student_count;
+-------+
| totle |
+-------+
| 4 |
+-------+
#示例2:职员表 (工号,姓名,性别,年龄) <===> 工资表 (工资编号,姓名,工资5000)
对职工进行添加时 工资表中也要体现当前职工的信息
对职工进行修改时 工资表中也要一并修改当前职工的信息
对职工进行解聘时 工资表中也要一并删除当前员工的工资信息
#创建职工表
CREATE TABLE tab11(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
sex ENUM('m','f'),
age INT
);
#创建工资表
CREATE TABLE tab22(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
salary DOUBLE(10,2)
);
#创建触发器1 INSERT 对职工进行添加时 工资表中也要体现当前职工的信息
CREATE TRIGGER tab11_insert_trigger
AFTER INSERT ON tab11
FOR EACH ROW
BEGIN
INSERT INTO tab22 VALUES (NULL,new.name,5000);
END;
测试用户新增
INSERT INTO tab11 VALUES (1,'AA','f',18);
INSERT INTO tab11 VALUES (2,'BB','m',20);
INSERT INTO tab11 VALUES (3,'CC','m',18);
mysql> SELECT * FROM tab22;
+----+------+---------+
| id | name | salary |
+----+------+---------+
| 1 | AA | 5000.00 |
| 2 | BB | 5000.00 |
| 3 | CC | 5000.00 |
+----+------+---------+
新增完成后我们检查tab2会发现有变化
#创建触发器2 UPDATE 对职工进行修改时 工资表中也要刷新当前职工的信息
CREATE TRIGGER tab11_update_trigger
AFTER UPDATE ON tab11
FOR EACH ROW
BEGIN
UPDATE tab22 SET name = new.name WHERE name = old.name;
END;
测试用户修改
mysql> SELECT * FROM tab22;
+----+------+---------+
| id | name | salary |
+----+------+---------+
| 1 | AA | 5000.00 |
| 2 | BB | 5000.00 |
| 3 | CC | 5000.00 |
+----+------+---------+
UPDATE tab11 SET name = 'FF' WHERE name = 'AA';
“SET name = 'FF'(新的) WHERE name = 'AA'(旧的,原来就有的);”
mysql> SELECT * FROM tab22;
+----+------+---------+
| id | name | salary |
+----+------+---------+
| 1 | FF | 5000.00 |
| 2 | BB | 5000.00 |
| 3 | CC | 5000.00 |
+----+------+---------+
修改完成后我们检查tab2会发现有变化
#创建触发器3 DELETE 对职工进行删除时 工资表中也要刷新当前职工信息
CREATE TRIGGER tab11_delete_trigger
AFTER DELETE ON tab11
FOR EACH ROW
BEGIN
DELETE FROM tab22 WHERE name = old.name;
END;
测试用户修改:
DELETE FROM tab11 WHERE name = 'BB';
mysql> SELECT * FROM tab11;
+----+------+------+------+
| id | name | sex | age |
+----+------+------+------+
| 1 | FF | f | 18 |
| 3 | CC | m | 18 |
+----+------+------+------+