MySQL 进阶三板斧（三）看清，java 高级框架思维导图
一、什么是触发器
========
触发器（trigger）：监视某种情况，并触发执行某种操作。触发器是在表中数据发生更改时自动触发执行的，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（insert，delete， update）时就会激活它执行。也就是说触发器只执行 DML 事件(insert、update 和 delete)
MySQL 触发器的作用
1. 安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过 10%。
2. 审计。可以跟踪用户对数据库的操作。 ??
审计用户操作数据库的语句。
把用户对数据库的更新写入审计表。
3. 实现复杂的数据完整性规则
实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
提供可变的缺省值。
4. 实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。
例如，在 auths 表 author_code 列上的删除触发器可导致相应删除在其它表中的与之匹配的行。
在修改或删除时级联修改或删除其它表中的与之匹配的行。
在修改或删除时把其它表中的与之匹配的行设成 NULL 值。
在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在 books.author_code 列上生成一个插入触发器，如果新值与 auths.author_code 列中的某值不匹配时，插入被回退。
5. 同步实时地复制表中的数据。
6. 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于 5 万元则立即给财务人员发送警告数据。
二、触发器语法
==========
1. 创建语法四要素
监视地点(table)
监视事件(insert | update | delete)
触发时间(after | before)
触发事件(insert | update | delete)
2. 语法公式
CREATE TRIGGER <trigger_name> ?
BEFORE|AFTER
INSERT|UPDATE|DELETE ?ON <table_name> # 表名
FOR EACH ROW ?# 这句话在 mysql 是固定的
BEGIN
<触发的 SQL 语句>（调用 NEW/OLD 参数）;
END
3. 语法参数说明
CREATE TRIGGER <trigger_name> ?--- 触发器必须有名字，最多 64 个字符，可能后面会附有分隔符.它和 MySQL 中其他对象的命名方式基本相象.
{ BEFORE | AFTER } ?--- 触发器触发时间设置：可以设置为事件发生前或后（前：一般用于校验；后：一般用于关联）。
{ INSERT | UPDATE | DELETE } ?-- 设定触发事件：如执行 insert、update 或 delete 的过程时激活触发器。
ON <table_name> ?--- 触发器是属于某一个表的: 当在这个表上执行 INSERT|UPDATE|DELETE 操作的时候就导致触发器的激活. 同时，我们不能给同一张表的“同一个事件”安排两个触发器（意味着不能同时有两个 Insert 触发器）。
FOR EACH ROW ?--- 触发器的执行间隔（必有的公式内容）：FOR EACH ROW 子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。
<触发的 SQL 语句> ?--- 触发器包含所要触发的 SQL 语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。当然，触发 SQL 中可以调用“触发了（ INSERT | UPDATE | DELETE ）触发器的那一行数据”。
例如下方代码：
create trigger add_stu
after insert on student for each row?
begin
insert into student_score ( stu_id, score, rank)
values( NEW.stuid, NEW.username); ?-- NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据
end;
4. NEW 与 OLD 关键字详解
MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：
① 在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
② 在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
③ 在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
另外，原则上请编写简单高效的触发执行语句，以免悄无声息的浪费过多资源你还不知道！
三、实际应用
======
1. 数据准备
想我初三时常年倒数，成绩稳定，因此我拿当时几位老友排名数据来纪念一波（手动挠头）。给大家提供一个测试数据；
（沿用的前两篇“视图”、“存储过程”博文中的数据）
a.学生表
CREATE TABLE student (
ID ?int NOT NULL AUTO_INCREMENT ?,
NAME ?varchar(30) NOT NULL ,
SEX ?char(2) NOT NULL ,
AGE ?int NOT NULL ,
CLASS ?varchar(10) NOT NULL ,
GRADE ?varchar(20) NOT NULL ,
HOBBY ?varchar(100) NULL ,
PRIMARY KEY (ID)
)
#插入数据：
INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES ('1', '陈哈哈', '男', '15', '18 班', '9 年级', '上网');
INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES ('2', '扈亚鹏', '男', '15', '18 班', '9 年级', '美食');
INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES ('3', '刘晓莉', '女', '14', '18 班', '9 年级', '金希澈');
INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES ('4', '朱志鹏', '男', '15', '18 班', '9 年级', '睡觉');
INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES ('5', '徐立楠', '女', '14', '18 班', '9 年级', '阅读');
INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES ('6', '顾昊', '男', '15', '5 班', '9 年级', '篮球');
INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES ('7', '陈子凝', '女', '15', '18 班', '9 年级', '看电影');
#插入结果：
b.成绩表
CREATE TABLE student_score (
SID int(11) NOT NULL,
S_NAME varchar(30) NOT NULL,
TOTAL_SCORE int(11) NOT NULL,
RANK int(11) NOT NULL,
PRIMARY KEY (SID)
)?
#插入数据：
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES ('1', '陈哈哈', '405', '1760');
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES ('2', '扈亚鹏', '497', '1000');
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES ('3', '刘晓莉', '488', '1170');
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES ('4', '朱志鹏', '405', '1770');
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES ('5', '徐立楠', '530', '701');
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES ('6', '顾昊', '485', '1286');
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES ('7', '陈子凝', '704', '9');
#插入结果：
c.逃课上网表
CREATE TABLE student_go_wangba (
SID int(9) NOT NULL,
SGW_NAME varchar(30) DEFAULT NULL,
TIMES int(9) DEFAULT NULL,
PRIMARY KEY (SID)
)?
#插入数据：
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES ('1', '陈哈哈', 15);
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES ('2', '扈亚鹏', 1);
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES ('3', '刘晓莉', 0);
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES ('4', '朱志鹏', 63);
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES ('5', '徐立楠', 0);
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES ('6', '顾昊', 7);
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES ('7', '陈子凝', 0);
#插入结果：
2. 使用案例
# Insert 触发器 - 级联插入
? ? ? ?表数据：以上面的三张表为例；学生表（student）、学生成绩表（student_score）、逃课上网次数表（student_go_wangba），均已学号（stuid）为主键。???
需求：
需要设计一个触发器 A，当增加新的学生时，需要在成绩表（student_score）中插入对应的学生信息，至于“分值、排名”字段为 0 即可；后面由老师打分更新。
需要设计一个触发器 B，当增加新的学生成绩信息时，需要在逃课上网表（student_go_wangba）中插入对应的学生信息，至于“逃课上网次数”字段为 0 即可；后面由教导主任“小平头”去更新。（该触发器意义在于：测试 after insert 链式反应是否支持）
那么，如何设计触发器 A 呢？
首先它是一个插入 Insert 触发器，是建立在表 student 上的；
然后是 after，插入后的事件；
事件内容是插入成绩表，需要插入学生的学号和姓名，number 为自增，而“分值、排名”目前不需要。
注意：new 表示 student 中新插入的值
触发器 A:
-- 新增触发器 A，当 student 表插入数据时，student_score 表生成初始关联数据
DROP TRIGGER IF EXISTS add_stu;
create trigger add_stu
after insert on student for each row?
begin
INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK)?
VALUES (new.ID,new.NAME, 0, 0 );
end;
触发器 B:
-- 新增触发器 B，当 student_score 表插入数据时，student_go_wangba 表生成初始关联数据
DROP TRIGGER IF EXISTS add_score;
create trigger add_score
after insert on student_score for each row?
begin
INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES)?
VALUES (new.SID,new.S_NAME, 0 );
end;
查询一下我的触发器：
show triggers \G
-- "\G"是干什么用的？
-- 作用：在 shell 中树形展示
如果在 Navicat 中就不用 \G，直接"show triggers;"就可以。?
还未添加个人签名 2021.03.18 加入
还未添加个人简介
评论