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

查看、修改与删除触发器

用两种方法查看实验一的触发器，再用先删除后重建的方法改进日志触发器，看 AFTER 级联删除为什么不行，最后删除触发器。

环　境

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

执　行

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

注　意

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

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

实验 2-1SHOW TRIGGERS
实验 2-2查询系统表
实验 2-3同名的触发器
实验 2-4先删除再重建
实验 2-5AFTER 级联删除
实验 2-6删除触发器

第一步

数据准备（必做）

重建 teaching 库的五张表，建立 update_s_log 表和实验一的三个触发器。

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

-- 第 16 章实验二数据：五张表、update_s_log 表和实验一建立的三个触发器
-- 整段执行一次即可，想从头再做时重新执行。
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');

-- 实验一建立的表和触发器
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 insert_sc_trigger
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
  IF NEW.score IS NULL THEN
    SET NEW.score = 0;
  END IF;
END $$
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 $$
CREATE TRIGGER delete_s_trigger
BEFORE DELETE ON s
FOR EACH ROW
BEGIN
  DELETE FROM sc
  WHERE sc.sno = OLD.sno;
END $$
DELIMITER ;
SHOW TRIGGERS;

2-1

SHOW TRIGGERS

讲义 · 查看触发器

目的: 查看 teaching 库中的全部触发器，再只看建在 s 表上的触发器。
前提: 已执行数据准备。

执行 SHOW TRIGGERS
用 LIKE 只看 s 表上的触发器

参考答案

可以直接复制到 Workbench 执行

SHOW TRIGGERS;
SHOW TRIGGERS LIKE 's';

语句解释

SHOW TRIGGERS	显示当前库中的触发器：名称、事件、表、语句、时机等
LIKE 's'	按表名匹配，不是按触发器名

执行后看：第一个查询 3 行。第二个查询 2 行：update_s_trigger 和 delete_s_trigger。

2-2

查询系统表

讲义 · 查看触发器

目的: 从系统表中查询触发器的名称、事件、表和时机，再单独查看 update_s_trigger 执行的语句。
前提: 已完成实验 2-1。

查询 teaching 库中所有触发器的四项信息
查询 update_s_trigger 的 ACTION_STATEMENT

参考答案

可以直接复制到 Workbench 执行

SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'teaching';
SELECT ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_NAME = 'update_s_trigger';

语句解释

information_schema.TRIGGERS	所有触发器的定义都存放在这张系统表中
EVENT_MANIPULATION	触发事件：INSERT、UPDATE 或 DELETE
ACTION_TIMING	触发时机：BEFORE 或 AFTER
ACTION_STATEMENT	触发器执行的语句块

执行后看：第一个查询 3 行。第二个查询得到 BEGIN … END 的全部内容。

2-3

同名的触发器

讲义 · 删除触发器

目的: 再建立一次 update_s_trigger，看能不能覆盖原来的触发器。
前提: 已完成实验 2-2。

原样再执行一次建立 update_s_trigger 的语句

参考答案

可以直接复制到 Workbench 执行

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 ;

语句解释

报错 1359	Trigger already exists，同名的触发器已经存在
不能覆盖	MySQL 没有修改触发器的语句，要改只能先删除再重建

执行后看：报错 1359。

2-4

先删除再重建

讲义 · 删除触发器讲义 · 例题 2：修改时记日志

目的: 改进实验 1-5 中的问题：专业真的改变时才记日志。先删除 update_s_trigger，再建立加了判断的新版本。
前提: 已完成实验 2-3。

删除 update_s_trigger
重建，只在 OLD.maj <> NEW.maj 时记日志
分别执行不改值和改值的 UPDATE，查看日志表

参考答案

可以直接复制到 Workbench 执行

DROP TRIGGER update_s_trigger;
DELIMITER $$
CREATE TRIGGER update_s_trigger
AFTER UPDATE ON s
FOR EACH ROW
BEGIN
  IF OLD.maj <> NEW.maj THEN
    INSERT INTO update_s_log
    VALUES (OLD.sno, OLD.sn, OLD.maj, NEW.maj, NOW());
  END IF;
END $$
DELIMITER ;
UPDATE s SET maj = maj WHERE sno IN ('s5', 's7');
UPDATE s SET maj = '计算机' WHERE sno = 's2';
SELECT * FROM update_s_log;

语句解释

DROP TRIGGER	删除触发器
IF OLD.maj <> NEW.maj	专业有变化才记日志
两次 UPDATE	第一次值没有变，不记。第二次 s2 由信息改为计算机，记一行

执行后看：日志表只有 1 行：s2 由信息改为计算机。

2-5

AFTER 级联删除

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

目的: 把 delete_s_trigger 改成 AFTER DELETE，再删除学生 s3，看为什么不行。
前提: 已完成实验 2-4。

删除 delete_s_trigger，重建为 AFTER DELETE
删除 s3
删除这个 AFTER 触发器

参考答案

可以直接复制到 Workbench 执行

DROP TRIGGER delete_s_trigger;
DELIMITER $$
CREATE TRIGGER delete_s_trigger
AFTER DELETE ON s
FOR EACH ROW
BEGIN
  DELETE FROM sc WHERE sc.sno = OLD.sno;
END $$
DELIMITER ;
-- s3 在 sc 中有成绩，报错 1451
DELETE FROM s WHERE sno = 's3';
DROP TRIGGER delete_s_trigger;

语句解释

执行顺序	BEFORE 触发器、删除这一行、AFTER 触发器
报错 1451	删除 s3 这一行时 sc 中还有它的成绩，外码约束不允许，AFTER 触发器来不及执行
结论	要先处理参照它的数据，就用 BEFORE

执行后看：报错 1451，s3 没有删除，它的成绩也还在。

2-6

删除触发器

讲义 · 删除触发器

目的: 删除 insert_sc_trigger。再建一张练习表和它上面的触发器，删除这张表，看触发器还在不在。
前提: 已完成实验 2-5。

用 DROP TRIGGER IF EXISTS 删除 insert_sc_trigger
建表 t_demo 和它的触发器
删除 t_demo，再查看触发器

参考答案

可以直接复制到 Workbench 执行

DROP TRIGGER IF EXISTS insert_sc_trigger;
CREATE TABLE t_demo (id INT);
-- 触发器体只有一条语句，可以不写 BEGIN END，也不用改定界符
CREATE TRIGGER t_demo_trigger BEFORE INSERT ON t_demo
FOR EACH ROW SET NEW.id = NEW.id + 1;
SHOW TRIGGERS;
DROP TABLE t_demo;
SHOW TRIGGERS;

语句解释

IF EXISTS	触发器不存在时不报错
权限	删除触发器要有这张表的 TRIGGER 权限
删除表	表上的触发器随表一起删除

执行后看：第一次 SHOW TRIGGERS 有 update_s_trigger 和 t_demo_trigger，第二次只剩 update_s_trigger。

上一节第三节　查看与删除触发器下一节第四节　事件与事件调度器

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

查看、修改与删除触发器

环 境

执 行

注 意

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

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

参考答案

语句解释

环　境

执　行

注　意

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