第 16 章 · 实验一 · 在第二节之后

创建触发器

建立三个触发器：插入成绩时把空成绩置 0，修改专业时记日志，删除学生时删除他的成绩。中间看 NEW 和 OLD 用错时的报错。

环　境

MySQL 8.0 和 MySQL Workbench，机房电脑已装好。所有语句在 Workbench 的 SQL 编辑区中执行。

执　行

Ctrl+Enter 执行光标所在的一句，Ctrl+Shift+Enter 执行全部。Mac 上把 Ctrl 换成 ⌘。

注　意

输入法切到英文。表名、字段名不加引号，字符串值才用单引号。

本次实验的顺序　后一个实验用到前一个的结果

实验 1-1BEFORE INSERT
实验 1-2只响应一种操作
实验 1-3用错 NEW 与 OLD
实验 1-4修改专业时记日志
实验 1-5每一行触发一次
实验 1-6删除学生时删除成绩

第一步

数据准备（必做）

重建 teaching 库的五张表和教材中的数据，删除本章实验建立的事件、存储过程和表。

整段复制，全部执行下载 .sql

-- 第 16 章实验数据：教材 teaching 库的五张表和教材中的数据
-- 实验一和实验三使用。整段执行一次即可，想从头再做时重新执行。
CREATE DATABASE IF NOT EXISTS teaching DEFAULT CHARACTER SET utf8mb4;
USE teaching;

-- 删除本章实验建立的事件、存储过程和表，表上的触发器随表一起删除
DROP EVENT IF EXISTS event_name1;
DROP EVENT IF EXISTS event_name2;
DROP EVENT IF EXISTS event_name3;
DROP EVENT IF EXISTS event_name4;
DROP EVENT IF EXISTS name4;
DROP EVENT IF EXISTS event_name5;
DROP EVENT IF EXISTS event_name6;
DROP PROCEDURE IF EXISTS pro_score;
DROP TABLE IF EXISTS update_s_log, tb_one, week_score, t_demo;

-- 删除旧的表。先暂时关闭外键检查，库里有别的表引用它们时也能删除
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS sc, tc, avgsal, classroom, s, c, t;
SET FOREIGN_KEY_CHECKS = 1;

-- 课程表
CREATE TABLE c (
  cno CHAR(4) NOT NULL,
  cn VARCHAR(30) NOT NULL,
  ct INT,
  PRIMARY KEY (cno)
);
-- 学生表
CREATE TABLE s (
  sno CHAR(4) NOT NULL,
  sn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  maj VARCHAR(30),
  dept VARCHAR(30),
  PRIMARY KEY (sno)
);
-- 教师表
CREATE TABLE t (
  tno CHAR(4) NOT NULL,
  tn VARCHAR(20) NOT NULL,
  sex CHAR(2),
  age INT,
  prof VARCHAR(20),
  maj VARCHAR(30),
  dept VARCHAR(30),
  sal DECIMAL(8,2),
  PRIMARY KEY (tno)
);
-- 选课表
CREATE TABLE sc (
  sno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  score DECIMAL(5,2),
  PRIMARY KEY (sno, cno),
  FOREIGN KEY (sno) REFERENCES s (sno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);
-- 授课表
CREATE TABLE tc (
  tno CHAR(4) NOT NULL,
  cno CHAR(4) NOT NULL,
  tcdate DATE,
  PRIMARY KEY (tno, cno),
  FOREIGN KEY (tno) REFERENCES t (tno),
  FOREIGN KEY (cno) REFERENCES c (cno)
);

INSERT INTO c VALUES
('c1','Java程序设计',40), ('c2','程序设计基础',48), ('c3','线性代数',48),
('c4','数据结构',64), ('c5','数据库系统',56), ('c6','数据挖掘',32),
('c7','高等数学',60), ('c8','控制理论',32);

INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s2','苏乐','男',20,'信息','信息学院'),
('s3','林欣怡','女',19,'信息','信息学院'),
('s4','陶然','男',19,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',18,'自动化','工学院');

INSERT INTO t VALUES
('t1','刘杨','男',38,'教授','计算机','信息学院',3800.00),
('t2','石丽','女',28,'讲师','计算机','信息学院',2800.00),
('t3','顾伟','男',35,'副教授','计算机','信息学院',3200.00),
('t4','赵礼','男',45,'教授','自动化','工学院',4267.90),
('t5','赵希希','女',33,'副教授','数学','理学院',3332.67),
('t6','张刚','男',30,'讲师','计算机','工学院',2600.00);

INSERT INTO sc VALUES
('s1','c1',90.50), ('s1','c2',88.00),
('s2','c4',70.00), ('s2','c5',57.00), ('s2','c6',81.50), ('s2','c7',NULL),
('s3','c1',75.00), ('s3','c2',76.50), ('s3','c4',85.00),
('s4','c1',93.00), ('s4','c2',82.00), ('s4','c6',NULL), ('s4','c7',68.00),
('s5','c2',79.00), ('s5','c7',84.00),
('s7','c3',77.00), ('s7','c5',62.00), ('s7','c7',100.00),
('s8','c2',66.00), ('s8','c3',96.00);

INSERT INTO tc VALUES
('t1','c1','2021-09-03'), ('t1','c2','2021-09-04'),
('t2','c5','2021-03-06'), ('t2','c6','2021-09-06'),
('t3','c2','2021-03-08'), ('t3','c4','2021-09-08'),
('t4','c3','2021-03-10'),
('t5','c7','2021-09-12'), ('t5','c8','2021-03-15');

1-1

BEFORE INSERT

讲义 · 创建触发器的语法讲义 · 例题 1：空成绩置 0

目的: 完成例题 1：建立触发器 insert_sc_trigger，向 sc 插入成绩为空的记录时，把成绩设置为 0。
前提: 已执行数据准备。

建立 insert_sc_trigger，BEFORE INSERT，作用于 sc
插入 ('s2', 'c8', NULL)
查询 s2 的选课记录

参考答案

可以直接复制到 Workbench 执行

DELIMITER $$
CREATE TRIGGER insert_sc_trigger
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
  IF NEW.score IS NULL THEN
    SET NEW.score = 0;
  END IF;
END $$
DELIMITER ;
INSERT INTO sc VALUES ('s2', 'c8', NULL);
SELECT * FROM sc WHERE sno = 's2';

语句解释

BEFORE INSERT ON sc	在向 sc 插入一行之前执行
FOR EACH ROW	每插入一行执行一次
NEW.score	将要插入的那一行的 score。在 BEFORE 触发器中可以修改它
DELIMITER $$	触发器的语句块中有分号，和存储过程一样要先改定界符

执行后看：s2 有 5 行，新插入的 c8 成绩为 0.00。

1-2

只响应一种操作

讲义 · 创建触发器的语法

目的: 把刚插入的成绩改为 NULL，看 insert_sc_trigger 会不会执行。
前提: 已完成实验 1-1。

把 s2 的 c8 成绩改为 NULL
再查询这一行

参考答案

可以直接复制到 Workbench 执行

UPDATE sc SET score = NULL WHERE sno = 's2' AND cno = 'c8';
SELECT * FROM sc WHERE sno = 's2' AND cno = 'c8';

语句解释

触发事件	一个触发器只响应 INSERT、UPDATE、DELETE 中的一种
UPDATE	insert_sc_trigger 只在插入时执行，修改时不执行
要管住修改	还要再建一个 BEFORE UPDATE 触发器

执行后看：成绩变成了 NULL，触发器没有执行。

1-3

用错 NEW 与 OLD

讲义 · 创建触发器的语法

目的: 建两个写错的触发器：一个在 AFTER 触发器中修改 NEW，一个在 INSERT 触发器中使用 OLD，看 MySQL 怎样报错。
前提: 已完成实验 1-2。

建立 AFTER INSERT 触发器 t_after，其中修改 NEW.score
建立 BEFORE INSERT 触发器 t_old，其中使用 OLD.score

参考答案

可以直接复制到 Workbench 执行

DELIMITER $$
-- AFTER 触发器中修改 NEW，报错 1362
CREATE TRIGGER t_after
AFTER INSERT ON sc
FOR EACH ROW
BEGIN
  SET NEW.score = 0;
END $$
-- INSERT 触发器中没有 OLD，报错 1363
CREATE TRIGGER t_old
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
  SET NEW.score = OLD.score;
END $$
DELIMITER ;

语句解释

报错 1362	AFTER 触发器执行时数据已经写入，不能再修改 NEW
报错 1363	INSERT 只有新行，没有旧行，所以没有 OLD
记住	INSERT 只有 NEW，DELETE 只有 OLD，UPDATE 两个都有

执行后看：两个触发器都没有建立，分别报错 1362 和 1363。

1-4

修改专业时记日志

讲义 · 例题 2：修改时记日志

目的: 完成例题 2：建立日志表 update_s_log 和触发器 update_s_trigger，修改学生的专业时，把修改前后的专业和修改时间记入日志表。
前提: 已完成实验 1-3。

建立 update_s_log
建立 update_s_trigger，AFTER UPDATE，作用于 s
把 s2 的专业改为计算机，查看日志表

参考答案

可以直接复制到 Workbench 执行

CREATE TABLE update_s_log (
  sno CHAR(5) NOT NULL,
  sn VARCHAR(10) NOT NULL,
  omaj VARCHAR(100) NOT NULL,
  nmaj VARCHAR(100) NOT NULL,
  udate DATETIME(0) NOT NULL
);
DELIMITER $$
CREATE TRIGGER update_s_trigger
AFTER UPDATE ON s
FOR EACH ROW
BEGIN
  INSERT INTO update_s_log
  VALUES (OLD.sno, OLD.sn, OLD.maj,
          NEW.maj, NOW());
END $$
DELIMITER ;
UPDATE s SET maj = '计算机' WHERE sno = 's2';
SELECT * FROM update_s_log;

语句解释

OLD.maj	修改前的专业
NEW.maj	修改后的专业
AFTER UPDATE	修改成功以后再记日志
udate DATETIME(0)	日期时间，精确到秒

执行后看：日志表 1 行：s2、苏乐、信息、计算机和当前时间。

1-5

每一行触发一次

讲义 · 例题 2：修改时记日志

目的: 把 s5、s7 的专业改成原来的值，实际上什么都没改，再看日志表。
前提: 已完成实验 1-4。

执行 UPDATE s SET maj = maj，条件为 s5 和 s7
查看日志表

参考答案

可以直接复制到 Workbench 执行

UPDATE s SET maj = maj WHERE sno IN ('s5', 's7');
SELECT * FROM update_s_log;

语句解释

FOR EACH ROW	UPDATE 找到几行，触发器就执行几次
值没有变	行级触发器照样执行，所以记下了两行无用的日志
怎样改进	在触发器中先判断 OLD.maj <> NEW.maj，实验 2-4 完成

执行后看：日志表 3 行，新增的 s5、s7 两行 omaj 和 nmaj 都是数学。

1-6

删除学生时删除成绩

讲义 · 例题 3：删除时级联删除

目的: 完成例题 3：建立触发器 delete_s_trigger，从 s 删除一个学生时，同时删除他在 sc 中的所有成绩。
前提: 已完成实验 1-5。

建立 delete_s_trigger，BEFORE DELETE，作用于 s
删除学生 s1
查询 s1 在 sc 中的成绩和学生人数

参考答案

可以直接复制到 Workbench 执行

DELIMITER $$
CREATE TRIGGER delete_s_trigger
BEFORE DELETE ON s
FOR EACH ROW
BEGIN
  DELETE FROM sc
  WHERE sc.sno = OLD.sno;
END $$
DELIMITER ;
DELETE FROM s WHERE sno = 's1';
SELECT * FROM sc WHERE sno = 's1';
SELECT COUNT(*) FROM s;

语句解释

OLD.sno	被删除的那个学生的学号
为什么用 BEFORE	sc 的 sno 是外码，要先删除成绩，学生才能删除
不加触发器时	直接删除有成绩的学生，外码约束报错 1451

执行后看：s1 在 sc 中已经没有记录，学生剩 7 人。

上一节第二节　创建触发器下一节第三节　查看与删除触发器

保山学院·人工智能教研室·曹鼎鼎

创建触发器

环 境

执 行

注 意

本次实验的顺序 后一个实验用到前一个的结果

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

环　境

执　行

注　意

本次实验的顺序　后一个实验用到前一个的结果