写点什么

MySQL 进阶三板斧(三)看清,java 高级框架思维导图

用户头像
极客good
关注
发布于: 刚刚

4. NEW 与 OLD关键字详解


三、实际应用


1. 数据准备


2. 使用案例


# Insert触发器 - 级联插入


# Delete触发器 - 级联删除


# Update触发器 - 实时更新


四、触发器有哪些缺陷 @江湖中人


五、总结




一、什么是触发器


========


触发器(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. 创建语法四要素




  1. 监视地点(table)

  2. 监视事件(insert | update | delete)

  3. 触发时间(after | before)

  4. 触发事件(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. 语法参数说明




  1. CREATE TRIGGER <trigger_name> ?--- 触发器必须有名字,最多 64 个字符,可能后面会附有分隔符.它和 MySQL 中其他对象的命名方式基本相象.

  2. { BEFORE | AFTER } ?--- 触发器触发时间设置:可以设置为事件发生前或后(前:一般用于校验;后:一般用于关联)。

  3. { INSERT | UPDATE | DELETE } ?-- 设定触发事件:如执行 insert、update 或 delete 的过程时激活触发器。

  4. ON <table_name> ?--- 触发器是属于某一个表的: 当在这个表上执行 INSERT|UPDATE|DELETE 操作的时候就导致触发器的激活. 同时,我们不能给同一张表的“同一个事件”安排两个触发器(意味着不能同时有两个 Insert 触发器)。

  5. FOR EACH ROW ?--- 触发器的执行间隔(必有的公式内容):FOR EACH ROW 子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。

  6. <触发的 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');


#插入结果:



【一线大厂Java面试题解析+核心总结学习笔记+最新架构讲解视频+实战项目源码讲义】
浏览器打开:qq.cn.hn/FTf 免费领取
复制代码


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 呢?


  1. 首先它是一个插入 Insert 触发器,是建立在表 student 上的;

  2. 然后是 after,插入后的事件;

  3. 事件内容是插入成绩表,需要插入学生的学号和姓名,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;"就可以。?

用户头像

极客good

关注

还未添加个人签名 2021.03.18 加入

还未添加个人简介

评论

发布
暂无评论
MySQL进阶三板斧(三)看清,java高级框架思维导图