【MySQL】实验06 触发器

avatar

azurekiln

  1. (简答题, 2分) 本实验的实验目的是:
    掌握触发器的创建、修改、删除及其使用方法；
    掌握触发器的功能。

  2. (简答题, 8分) 请写出下列的SQL程序代码：“在Students上创建一个UPDATE触发器Trig_UpdStuBirth_学号最后两位，要求：当更新学生的出生日期时，检查此学生的入学年龄是否是在14~40岁之间，若是则允许更新，若不是则提示错误信息‘学生的出生日期有误，请确认后重新输入！’。”

    DELIMITER //
CREATE TRIGGER Trig_UpdStuBirth_59
BEFORE UPDATE ON Students59
FOR EACH ROW
BEGIN
DECLARE v_age INT;
IF NEW.Sbirth != OLD.Sbirth THEN
    SET v_age = YEAR(CONCAT(LEFT(NEW.Sno, 4), '-09-01')) - YEAR(NEW.Sbirth);
    IF v_age < 14 OR v_age > 40 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '学生的出生日期有误，请确认后重新输入！';
    END IF;
END IF;
END //
DELIMITER ;

  3. (简答题, 4分)
    针对UPDATE触发器Trig_UpdStuBirth_学号最后两位，分别设计并写出正反例的SQL语句各一条，即一条语句执行后允许更改，一条语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
    提示：执行正反例程序时，最好给出语句执行前后的结果对照

    # 正例：更新后年龄在14~40岁之间（假设学号202003005101，入学年份2020）
# 执行前查询
SELECT Sno, Sname, Sbirth FROM Students59 WHERE Sno = '202003005103';
# 正例SQL（设置出生日期使入学年龄为20岁）
UPDATE Students59 SET Sbirth = '2000-05-15' WHERE Sno = '202003005103';
# 执行后查询
SELECT Sno, Sname, Sbirth FROM Students59 WHERE Sno = '202003005103';
# 反例：更新后年龄不在14~40岁之间
# 反例SQL（设置出生日期使入学年龄不足10岁，不符合要求）
UPDATE Students59 SET Sbirth = '2022-05-15' WHERE Sno = '202003005103';

  4. (简答题, 8分)
    请写出下列的SQL程序代码："在Students表上创建一个INSERT触发器Trig_InsStuSno_学号最后两位，要求：当插入一条学生记录时，首先判断学生的学号长度是否为12，然后再判断，输入的学号是否都是数字。若两个条件都成立，则允许执行INSERT语句，否则，要分别给出错误提示信息"
    提示：可以使用正则表达式来判断输入的学号是否都是数字。

    DELIMITER //
CREATE TRIGGER Trig_InsStuSno_59
BEFORE INSERT ON Students59
FOR EACH ROW
BEGIN
IF LENGTH(NEW.Sno) != 12 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '学号长度必须为12位！';
END IF;
IF NEW.Sno NOT REGEXP '^[0-9]+$' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '学号必须全部为数字！';
END IF;
END //
DELIMITER ;

  5. (简答题, 6分)
    针对INSERT触发器Trig_InsStuSno_学号最后两位，分别设计并写出正例的SQL语句一条，反例的SQL语句两条（分别对应两个判断），即正例语句执行后允许插入，反例语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
    提示：执行正反例程序时，最好给出语句执行前后的结果对照

    # 正例：学号长度为12且全为数字
INSERT INTO Students59 (Sno, Sname, Sgender, Sbirth, Sclass, Snative) VALUES ('202312345678', '测试学生', '男', '2003-01-01', '2023030051', '河南省郑州市');
# 执行后查询
SELECT * FROM Students59 WHERE Sno = '202312345678';
# 反例1：学号长度不为12
INSERT INTO Students59 (Sno, Sname, Sgender, Sbirth, Sclass, Snative) VALUES ('20231234', '测试学生2', '男', '2003-01-01', '2023030051', '河南省郑州市');
# 反例2：学号包含非数字字符
INSERT INTO Students59 (Sno, Sname, Sgender, Sbirth, Sclass, Snative) VALUES ('2023ABC45678', '测试学生3', '男', '2003-01-01', '2023030051', '河南省郑州市');


  6. (简答题, 8分) 请写出下列的SQL程序代码：
    “在Teachers表上创建一个UPDATE触发器Trig_UpdTeaProf_学号最后两位，要求：首先确认更新的职称是否正确，即只能输入'助教'、'讲师'、'副教授'或'教授'，若输入错误，则系统提示错误信息；当职称从'助教'晋升为'讲师'时，岗位津贴(TComm)增加300元；当职称从'讲师'晋升为'副教授'时，岗位津贴(TComm)增加500元；当职称从'副教授'晋升为'教授'时，岗位津贴自动增加900元；不允许越级晋升，即不能从助教直接升级为副教授，不能从讲师直接升级为教授；也不能降级。”

    DELIMITER //
CREATE TRIGGER Trig_UpdTeaProf_59
BEFORE UPDATE ON Teachers59
FOR EACH ROW
BEGIN
IF NEW.Tprof IS NOT NULL AND NEW.Tprof NOT IN ('助教', '讲师', '副教授', '教授') THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '职称输入错误，只能输入助教、讲师、副教授或教授！';
END IF;
IF OLD.Tprof != NEW.Tprof THEN
    IF OLD.Tprof = '助教' AND NEW.Tprof = '讲师' THEN
        SET NEW.Tcomm = IFNULL(OLD.Tcomm, 0) + 300;
    ELSEIF OLD.Tprof = '讲师' AND NEW.Tprof = '副教授' THEN
        SET NEW.Tcomm = IFNULL(OLD.Tcomm, 0) + 500;
    ELSEIF OLD.Tprof = '副教授' AND NEW.Tprof = '教授' THEN
        SET NEW.Tcomm = IFNULL(OLD.Tcomm, 0) + 900;
    ELSEIF OLD.Tprof = '助教' AND NEW.Tprof IN ('副教授', '教授') THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '不允许越级晋升！';
    ELSEIF OLD.Tprof = '讲师' AND NEW.Tprof = '教授' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '不允许越级晋升！';
    ELSEIF (OLD.Tprof = '教授' AND NEW.Tprof IN ('副教授', '讲师', '助教'))
        OR (OLD.Tprof = '副教授' AND NEW.Tprof IN ('讲师', '助教'))
        OR (OLD.Tprof = '讲师' AND NEW.Tprof = '助教') THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '不允许降级！';
    END IF;
END IF;
END //
DELIMITER ;

  7. (简答题, 4分)
    针对UPDATE触发器Trig_UpdTeaTpro_学号最后两位，设计并写出反例的SQL语句两条，如修改的职称写成了'jiangshi'，原有的职称'助教'改成了'副教授'，语句执行后，系统提示出错信息。要求将执行结果截图，截图中写入学号后两位。
    提示：执行正反例程序时，最好给出语句执行前后的结果对照

    # 执行前查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers59 WHERE Tprof = '助教' LIMIT 1;
# 反例1：职称输入错误
UPDATE Teachers59 SET Tprof = 'jiangshi' WHERE Tno = 'T001';
# 反例2：越级晋升（助教直接升副教授）
UPDATE Teachers59 SET Tprof = '副教授' WHERE Tprof = '助教' LIMIT 1;


  8. (简答题, 4分) 针对UPDATE触发器Trig_UpdTeaTpro_学号最后两位，设计并写出正例的SQL语句一条，如'讲师'改为'副教授'，提示：执行语句后，系统还是会提示出错信息，但提示的信息似乎与触发器中的信息不同，将执行结果截图，并分析原因，截图中写入学号后两位。

    # 执行前查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers59 WHERE Tprof = '讲师' LIMIT 1;
# 正例：讲师晋升为副教授
UPDATE Teachers59 SET Tprof = '副教授' WHERE Tprof = '讲师' LIMIT 1;

这个错误信息表明CHECK约束chk_salary被违反了，与触发器Trig_UpdTeaTpro_59无关。原因是因为更新了职称导致教师工资Tsal与职称不满足表中定义的CHECK约束条件。

  1. (简答题, 10分)
    请写出更改后的Teachers表上的UPDATE触发器Trig_UpdTeaTpro_学号最后两位的SQL程序，使得语句能正确执行，即给出正例SQL语句一条，当职称'讲师'改为'副教授'时，语句能正确执行。将执行结果截图，截图中写入学号后两位。
    提示：（1）在UPDATE触发器中，加入新的赋值语句，以确保满足系统提示出错的约束条件即可；（2）执行正反例程序时，最好给出语句执行前后的结果对照

    # 先删除原触发器
DROP TRIGGER IF EXISTS Trig_UpdTeaProf_59;
# 创建修改后的触发器
DELIMITER //
CREATE TRIGGER Trig_UpdTeaProf_59
BEFORE UPDATE
ON Teachers59
FOR EACH ROW
BEGIN
IF NEW.Tprof IS NOT NULL AND NEW.Tprof NOT IN ('助教', '讲师', '副教授', '教授') THEN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '职称输入错误，只能输入助教、讲师、副教授或教授！';
END IF;
# 确保Tcomm不为NULL
IF NEW.Tcomm IS NULL THEN
    SET NEW.Tcomm = 0;
END IF;
IF OLD.Tprof != NEW.Tprof THEN
    IF OLD.Tprof = '助教' AND NEW.Tprof = '讲师' THEN
        SET NEW.Tcomm = IFNULL(OLD.Tcomm, 0) + 300;
        # 工资调整到讲师范围
        IF NEW.Tsal < 1600 THEN SET NEW.Tsal = 1600; END IF;
        IF NEW.Tsal > 2600 THEN SET NEW.Tsal = 2600; END IF;
    ELSEIF OLD.Tprof = '讲师' AND NEW.Tprof = '副教授' THEN
        SET NEW.Tcomm = IFNULL(OLD.Tcomm, 0) + 500;
        # 工资调整到副教授范围
        IF NEW.Tsal < 2600 THEN SET NEW.Tsal = 2600; END IF;
        IF NEW.Tsal > 3500 THEN SET NEW.Tsal = 3500; END IF;
    ELSEIF OLD.Tprof = '副教授' AND NEW.Tprof = '教授' THEN
        SET NEW.Tcomm = IFNULL(OLD.Tcomm, 0) + 900;
        # 工资调整到教授范围（>3500）
        IF NEW.Tsal <= 3500 THEN SET NEW.Tsal = 3501; END IF;
    ELSEIF OLD.Tprof = '助教' AND NEW.Tprof IN ('副教授', '教授') THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '不允许越级晋升！';
    ELSEIF OLD.Tprof = '讲师' AND NEW.Tprof = '教授' THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '不允许越级晋升！';
    ELSEIF (OLD.Tprof = '教授' AND NEW.Tprof IN ('副教授', '讲师', '助教'))
        OR (OLD.Tprof = '副教授' AND NEW.Tprof IN ('讲师', '助教'))
        OR (OLD.Tprof = '讲师' AND NEW.Tprof = '助教') THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '不允许降级！';
    END IF;
END IF;
END //
DELIMITER ;
# 执行前查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers59 WHERE Tprof = '讲师' LIMIT 1;
# 正例SQL
UPDATE Teachers59 SET Tprof = '副教授' WHERE Tno = (SELECT t.Tno FROM (SELECT Tno FROM Teachers59 WHERE Tprof = '讲师' LIMIT 1) t);
# 执行后查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers59 WHERE Tprof = '副教授' ORDER BY Tcomm DESC LIMIT 1;

  2. (简答题, 8分) 请写出下列的SQL程序代码："在Tutors表上创建一个INSERT触发器Trig_InsTutCno_学号最后两位，要求：当插入一条教师的授课记录时，首先判断是否已经有教师给当前班级讲授过该门课程了（无论哪个学期），若有，则系统提示出错信息，不允许执行INSERT语句，若没有，则允许执行语句，且在选课表中自动插入该班所有学生选修该门课程的选课信息（要注意选修学年和选修学期也要对应）。"

    DELIMITER //
CREATE TRIGGER Trig_InsTutCno_59
BEFORE INSERT ON Tutors59
FOR EACH ROW
BEGIN
DECLARE v_count INT;
SELECT COUNT(*) INTO v_count
FROM Tutors59
WHERE Cno = NEW.Cno AND Sclass = NEW.Sclass;
IF v_count > 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '该班级已有教师讲授此课程，不允许重复添加！';
END IF;
END //
DELIMITER ;
# 创建AFTER触发器用于插入选课记录
DELIMITER //
CREATE TRIGGER Trig_InsTutCno_After_59
AFTER INSERT ON Tutors59
FOR EACH ROW
BEGIN
INSERT INTO Reports59 (Sno, Cno, Racademicyear, Rterm, Grade)
SELECT S.Sno, NEW.Cno,NEW.Tacademicyear, NEW.Tterm, NULL
FROM Students59 S
WHERE S.Sclass = NEW.Sclass
AND NOT EXISTS (
    SELECT 1 FROM Reports59 R WHERE R.Sno = S.Sno AND R.Cno = NEW.Cno
);
END //
DELIMITER ;

  3. (简答题, 7分)
    针对INSERT触发器Trig_InsTutCno_学号最后两位，设计并写出正例的SQL语句一条，即正例语句执行后允许插入授课信息，且自动在Reports表中插入学生的选课信息；设计并写出反例的SQL语句一条，即反例语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
    提示：执行正反例程序时，最好给出语句执行前后的结果对照

    # 正例：添加新的授课记录
# 执行前查询
SELECT * FROM Tutors59 WHERE Sclass = '2023030051' AND Cno = 'C001';
SELECT COUNT(*) FROM Reports59 WHERE Cno = 'C001' AND Sno IN (SELECT Sno FROM Students59 WHERE Sclass = '2023030051');
# 正例SQL（假设C001课程未在2023030051班开设过）
INSERT INTO Tutors59 (Tno, Cno, Tacademicyear, Tterm, Sclass) VALUES ('T001', '152g1003', 2024, 1, '2023030051');
# 执行后查询
SELECT * FROM Tutors59 WHERE Sclass = '2023030051' AND Cno = '152g1003';
SELECT * FROM Reports59 WHERE Cno = 'C001' AND Sno IN (SELECT Sno FROM Students59 WHERE Sclass = '2023030051');
# 反例：重复添加授课记录
INSERT INTO Tutors59 (Tno, Cno, Tacademicyear, Tterm, Sclass) VALUES ('T002', '152g1003', 2024, 2, '2023030051');

  4. (简答题, 8分) 请写出下列的SQL程序代码：“在Courses表上创建一个DELETE触发器Trig_DelCou，要求：当要删除一门课程时，首先在选课表中查看是否已有学生选修了该门课程，若有且已有成绩，则系统提示出错信息，不允许删除；若还没有学生选修，或是有学生选修了但成绩为空，则先将选课表中该门课程的选修记录删除掉，并将授课表中该门课程的授课记录也删除掉，同时允许删除课程表中的课程记录。”

    DELIMITER //
CREATE TRIGGER Trig_DelCou_59
BEFORE DELETE ON Courses59
FOR EACH ROW
BEGIN
DECLARE v_hasGrade INT;
SELECT COUNT(*) INTO v_hasGrade
FROM Reports59
WHERE Cno = OLD.Cno AND Grade IS NOT NULL;
IF v_hasGrade > 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '该课程已有学生成绩，不允许删除！';
END IF;
DELETE FROM Reports59 WHERE Cno = OLD.Cno;
DELETE FROM Tutors59 WHERE Cno = OLD.Cno;
END //
DELIMITER ;

  5. (简答题, 10分)
    针对DELETE触发器Trig_DelCou_学号最后两位，设计并写出正例的SQL语句一条，提示：执行语句前，可先在课程表中加入一门新的课程信息，然后在授课表中添加一条该门课程的授课信息后（因已创建了授课表上的Trig_InsTutor，因此系统自动在选课表中加入了该门课程的选课信息，将选课信息查出来并截图），再执行删除课程的语句；设计并写出反例的SQL语句一条，语句执行后，系统还是会提示出错信息。将执行结果截图，截图中写入学号后两位。

例：添加课程信息：课程编号：'112p0068'，课程名称：'软件工程'，前序课程编号：'112p0046'，课时：80，学分：4

   添加授课信息：教师编号：'T005'，课程编号：'112p0068'，授课学年：2018，授课学期：1，授课班级：20161151
# 正例准备：添加新课程
INSERT INTO Courses59 (Cno, Cname, Pre_Cno, Chours, Ccredit) VALUES ('112p0069', '软件工程', '112p0046', 80, 4);
# 添加授课信息（会自动触发选课记录插入）
INSERT INTO Tutors59 (Tno, Cno, Tacademicyear, Tterm, Sclass) VALUES ('T005', '112p0069', 2018, 1, '2021160261');
# 查询选课信息（截图用）
SELECT * FROM Reports59 WHERE Cno = '112p0069';
# 正例SQL：删除课程（选课记录无成绩）
DELETE FROM Courses59 WHERE Cno = '112p0069';
# 执行后查询
SELECT * FROM Courses59 WHERE Cno = '112p0069';
SELECT * FROM Reports59 WHERE Cno = '112p0069';
SELECT * FROM Tutors59 WHERE Cno = '112p0069';
# 反例SQL：删除已有成绩的课程
DELETE FROM Courses59 WHERE Cno = (
    SELECT DISTINCT Cno FROM Reports59 WHERE Grade IS NOT NULL LIMIT 1
);
  1. (简答题, 8分)

请写出下列的SQL程序代码：

“（1）创建一个学分表StuCredits，包含两个属性列(学号Sno，总学分TotalCredits)，用来保存学生已获得的总学分数，其中学号定义为学分表的主键，同时也是外键，被参照表为Students。
（2）在选课表Reports上创建一个UPDATE触发器Trig_UpdRepGrade，要求：当修改某位学生的选课成绩Grade时，统计该名学生获得的总学分，并将结果保存到StuCredits表中
① 当StuCredits表没有该位学生的学分信息时，在学分表中插入该位学生已获得的总学分；
② 当StuCredits表已有该位学生的学分信息时，则更新学分表中该位学生已获得的总学分。

#（1）创建学分表
CREATE TABLE StuCredits (
    Sno VARCHAR(20) PRIMARY KEY,
    TotalCredits DECIMAL(5,1) DEFAULT 0,
    FOREIGN KEY (Sno) REFERENCES Students59(Sno)
);
#（2）创建UPDATE触发器
DELIMITER //
CREATE TRIGGER Trig_UpdRepGrade_59
AFTER UPDATE ON Reports59
FOR EACH ROW
BEGIN
    DECLARE v_total DECIMAL(5,1);
    DECLARE v_exists INT;
    SELECT IFNULL(SUM(C.Ccredit), 0) INTO v_total
    FROM Reports59 R
    JOIN Courses59 C ON R.Cno = C.Cno
    WHERE R.Sno = NEW.Sno AND R.Grade >= 60;
    SELECT COUNT(*) INTO v_exists FROM StuCredits WHERE Sno = NEW.Sno;
    IF v_exists = 0 THEN
        INSERT INTO StuCredits (Sno, TotalCredits) VALUES (NEW.Sno, v_total);
    ELSE
        UPDATE StuCredits SET TotalCredits = v_total WHERE Sno = NEW.Sno;
    END IF;
END //
DELIMITER ;
  1. (简答题, 4分)

针对UPDATE触发器Trig_UpdRepGrade_学号最后两位，设计并写出以下两条SQL语句，并将执行结果截图，截图中写入学号后两位。

（1）在Reports表中找到有选课成绩为空的学生学号，写出将该位学生的选课成绩改为76的更新语句，并将学分表中的结果截图；

（2）在Reports表中将（1）中的学生的另一门选课的成绩做一下修改，如将另一门成绩为空的选课成绩设置为60分以上的成绩，或是将某一门不及格的成绩改为60分以上的成绩，或是将一门60分以上的成绩改为60分一下，写出对应的更新语句，并将学分表中的结果截图。

提示：本触发器需要设置为AFTER UPDATE，请大家考虑一下，这是为什么？

# （1）找到成绩为空的学生，更新成绩为76
# 查询成绩为空的记录
SELECT R.Sno, S.Sname, R.Cno, C.Cname, R.Grade FROM Reports59 R JOIN Students59 S ON R.Sno = S.Sno
JOIN Courses59 C ON R.Cno = C.Cno WHERE R.Grade IS NULL LIMIT 1;
# 更新成绩（假设找到的学号为202003005101，课程号为C001）
UPDATE Reports59 SET Grade = 76 WHERE Sno = '202003005101' AND Cno = 'C001' AND Grade IS NULL;
# 查询学分表结果
SELECT SC.*, S.Sname FROM StuCredits SC JOIN Students59 S ON SC.Sno = S.Sno WHERE SC.Sno = '202003005101';
# （2）修改该学生另一门课程的成绩
# 查询该学生其他课程
SELECT R.Sno, R.Cno, C.Cname, R.Grade FROM Reports59 R JOIN Courses59 C ON R.Cno = C.Cno
WHERE R.Sno = '202003005101';
# 更新另一门成绩（假设课程号为C002）
UPDATE Reports59 SET Grade = 85 WHERE Sno = '202003005101' AND Cno = 'C002';
# 查询学分表结果
SELECT SC.*, S.Sname FROM StuCredits SC JOIN Students59 S ON

扫描二维码，在手机上阅读
收藏
上一篇 章节测验
下一篇 【MySQL】实验07 数据库的安全性保护
powered by emlog pro
服务器供应商 新区云数据

友情链接
白衣Ink


sitemap