【MySQL】实验06 触发器

avatar

azurekiln

  1. (简答题, 2分) 本实验的实验目的是:

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

    DELIMITER //
CREATE TRIGGER Trig_UpdStuBirth_XX
BEFORE UPDATE ON Students
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 Students WHERE Sno = '202003005101';

正例SQL（设置出生日期使入学年龄为20岁）

UPDATE Students SET Sbirth = '2000-05-15' WHERE Sno = '202003005101';

执行后查询

SELECT Sno, Sname, Sbirth FROM Students WHERE Sno = '202003005101';

反例：更新后年龄不在14~40岁之间

反例SQL（设置出生日期使入学年龄为10岁，不符合要求）

UPDATE Students SET Sbirth = '2010-05-15' WHERE Sno = '202003005101';


5. (简答题, 8分)
请写出下列的SQL程序代码："在Students表上创建一个INSERT触发器Trig_InsStuSno_学号最后两位，要求：当插入一条学生记录时，首先判断学生的学号长度是否为12，然后再判断，输入的学号是否都是数字。若两个条件都成立，则允许执行INSERT语句，否则，要分别给出错误提示信息"
提示：可以使用正则表达式来判断输入的学号是否都是数字。
```sql
DELIMITER //
CREATE TRIGGER Trig_InsStuSno_XX
BEFORE INSERT ON Students
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 ;
  1. (简答题, 6分)
    针对INSERT触发器Trig_InsStuSno_学号最后两位，分别设计并写出正例的SQL语句一条，反例的SQL语句两条（分别对应两个判断），即正例语句执行后允许插入，反例语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
    提示：执行正反例程序时，最好给出语句执行前后的结果对照 
    
-- 正例：学号长度为12且全为数字
INSERT INTO Students (Sno, Sname, Ssex, Sbirth, Classno, Snative)
VALUES ('202312345678', '测试学生', '男', '2003-01-01', '2023030051', '河南省郑州市');

-- 执行后查询
SELECT * FROM Students WHERE Sno = '202312345678';

-- 反例1：学号长度不为12
INSERT INTO Students (Sno, Sname, Ssex, Sbirth, Classno, Snative)
VALUES ('20231234', '测试学生2', '男', '2003-01-01', '2023030051', '河南省郑州市');

-- 反例2：学号包含非数字字符
INSERT INTO Students (Sno, Sname, Ssex, Sbirth, Classno, Snative)
VALUES ('2023ABC45678', '测试学生3', '男', '2003-01-01', '2023030051', '河南省郑州市');


7. (简答题, 8分) 请写出下列的SQL程序代码：
“在Teachers表上创建一个UPDATE触发器Trig_UpdTeaProf_学号最后两位，要求：首先确认更新的职称是否正确，即只能输入'助教'、'讲师'、'副教授'或'教授'，若输入错误，则系统提示错误信息；当职称从'助教'晋升为'讲师'时，岗位津贴(TComm)增加300元；当职称从'讲师'晋升为'副教授'时，岗位津贴(TComm)增加500元；当职称从'副教授'晋升为'教授'时，岗位津贴自动增加900元；不允许越级晋升，即不能从助教直接升级为副教授，不能从讲师直接升级为教授；也不能降级。”
```sql
DELIMITER //
CREATE TRIGGER Trig_UpdTeaProf_XX
BEFORE UPDATE ON Teachers
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 ;
  1. (简答题, 4分)
    针对UPDATE触发器Trig_UpdTeaTpro_学号最后两位，设计并写出反例的SQL语句两条，如修改的职称写成了'jiangshi'，原有的职称'助教'改成了'副教授'，语句执行后，系统提示出错信息。要求将执行结果截图，截图中写入学号后两位。
    提示：执行正反例程序时，最好给出语句执行前后的结果对照 
    
-- 执行前查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers WHERE Tprof = '助教' LIMIT 1;

-- 反例1：职称输入错误
UPDATE Teachers SET Tprof = 'jiangshi' WHERE Tno = 'T001';

-- 反例2：越级晋升（助教直接升副教授）
UPDATE Teachers SET Tprof = '副教授' WHERE Tprof = '助教' LIMIT 1;


9. (简答题, 4分) 针对UPDATE触发器Trig_UpdTeaTpro_学号最后两位，设计并写出正例的SQL语句一条，如'讲师'改为'副教授'，提示：执行语句后，系统还是会提示出错信息，但提示的信息似乎与触发器中的信息不同，将执行结果截图，并分析原因，截图中写入学号后两位。
```sql
-- 执行前查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers WHERE Tprof = '讲师' LIMIT 1;

-- 正例：讲师晋升为副教授
UPDATE Teachers SET Tprof = '副教授' WHERE Tprof = '讲师' LIMIT 1;
  1. (简答题, 10分)

请写出更改后的Teachers表上的UPDATE触发器Trig_UpdTeaTpro_学号最后两位的SQL程序，使得语句能正确执行，即给出正例SQL语句一条，当职称'讲师'改为'副教授'时，语句能正确执行。将执行结果截图，截图中写入学号后两位。

提示：（1）在UPDATE触发器中，加入新的赋值语句，以确保满足系统提示出错的约束条件即可；（2）执行正反例程序时，最好给出语句执行前后的结果对照

-- 先删除原触发器
DROP TRIGGER IF EXISTS Trig_UpdTeaProf_XX;

-- 创建修改后的触发器
DELIMITER //
CREATE TRIGGER Trig_UpdTeaProf_XX
BEFORE UPDATE ON Teachers
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;
        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 ;

-- 测试正例
-- 执行前查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers WHERE Tprof = '讲师' LIMIT 1;

-- 正例SQL
UPDATE Teachers SET Tprof = '副教授' WHERE Tno = (SELECT t.Tno FROM (SELECT Tno FROM Teachers WHERE Tprof = '讲师' LIMIT 1) t);

-- 执行后查询
SELECT Tno, Tname, Tprof, Tcomm FROM Teachers WHERE Tprof = '副教授' ORDER BY Tcomm DESC LIMIT 1;

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

    
DELIMITER //
CREATE TRIGGER Trig_InsTutCno_XX
BEFORE INSERT ON Tutors
FOR EACH ROW
BEGIN
DECLARE v_count INT;

SELECT COUNT(*) INTO v_count
FROM Tutors
WHERE Cno = NEW.Cno AND Tclassno = NEW.Tclassno;

IF v_count > 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '该班级已有教师讲授此课程，不允许重复添加！';
END IF;
END //
DELIMITER ;

-- 创建AFTER触发器用于插入选课记录
DELIMITER //
CREATE TRIGGER Trig_InsTutCno_After_XX
AFTER INSERT ON Tutors
FOR EACH ROW
BEGIN
INSERT INTO Reports (Sno, Cno, Grade)
SELECT S.Sno, NEW.Cno, NULL
FROM Students S
WHERE S.Classno = NEW.Tclassno
AND NOT EXISTS (
SELECT 1 FROM Reports R WHERE R.Sno = S.Sno AND R.Cno = NEW.Cno
);
END //
DELIMITER ;


12. (简答题, 7分)
针对INSERT触发器Trig_InsTutCno_学号最后两位，设计并写出正例的SQL语句一条，即正例语句执行后允许插入授课信息，且自动在Reports表中插入学生的选课信息；设计并写出反例的SQL语句一条，即反例语句执行后，系统提示出错。要求将执行结果截图，截图中写入学号后两位。
提示：执行正反例程序时，最好给出语句执行前后的结果对照
```sql
-- 正例：添加新的授课记录
-- 执行前查询
SELECT * FROM Tutors WHERE Tclassno = '2023030051' AND Cno = 'C001';
SELECT COUNT(*) FROM Reports WHERE Cno = 'C001' AND Sno IN (SELECT Sno FROM Students WHERE Classno = '2023030051');

-- 正例SQL（假设C001课程未在2023030051班开设过）
INSERT INTO Tutors (Tno, Cno, Tacademicyear, Tterm, Tclassno)
VALUES ('T001', 'C001', 2024, 1, '2023030051');

-- 执行后查询
SELECT * FROM Tutors WHERE Tclassno = '2023030051' AND Cno = 'C001';
SELECT * FROM Reports WHERE Cno = 'C001' AND Sno IN (SELECT Sno FROM Students WHERE Classno = '2023030051');

-- 反例：重复添加授课记录
INSERT INTO Tutors (Tno, Cno, Tacademicyear, Tterm, Tclassno)
VALUES ('T002', 'C001', 2024, 2, '2023030051');

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

    DELIMITER //
CREATE TRIGGER Trig_DelCou_XX
BEFORE DELETE ON Courses
FOR EACH ROW
BEGIN
DECLARE v_hasGrade INT;

SELECT COUNT(*) INTO v_hasGrade
FROM Reports
WHERE Cno = OLD.Cno AND Grade IS NOT NULL;

IF v_hasGrade > 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '该课程已有学生成绩，不允许删除！';
END IF;

DELETE FROM Reports WHERE Cno = OLD.Cno;
DELETE FROM Tutors WHERE Cno = OLD.Cno;
END //
DELIMITER ;

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

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

   添加授课信息：教师编号：'T005'，课程编号：'112p0068'，授课学年：2018，授课学期：1，授课班级：20161151
-- 正例准备：添加新课程
INSERT INTO Courses (Cno, Cname, Cpno, Period, Credit)
VALUES ('112p0068', '软件工程', '112p0046', 80, 4);

-- 添加授课信息（会自动触发选课记录插入）
INSERT INTO Tutors (Tno, Cno, Tacademicyear, Tterm, Tclassno)
VALUES ('T005', '112p0068', 2018, 1, '2021160261');

-- 查询选课信息（截图用）
SELECT * FROM Reports WHERE Cno = '112p0068';

-- 正例SQL：删除课程（选课记录无成绩）
DELETE FROM Courses WHERE Cno = '112p0068';

-- 执行后查询
SELECT * FROM Courses WHERE Cno = '112p0068';
SELECT * FROM Reports WHERE Cno = '112p0068';
SELECT * FROM Tutors WHERE Cno = '112p0068';

-- 反例SQL：删除已有成绩的课程
DELETE FROM Courses WHERE Cno = (
    SELECT DISTINCT Cno FROM Reports 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 Students(Sno)
);

-- （2）创建UPDATE触发器
DELIMITER //
CREATE TRIGGER Trig_UpdRepGrade_XX
AFTER UPDATE ON Reports
FOR EACH ROW
BEGIN
    DECLARE v_total DECIMAL(5,1);
    DECLARE v_exists INT;

    SELECT IFNULL(SUM(C.Credit), 0) INTO v_total
    FROM Reports R
    JOIN Courses 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 Reports R
JOIN Students S ON R.Sno = S.Sno
JOIN Courses C ON R.Cno = C.Cno
WHERE R.Grade IS NULL
LIMIT 1;

-- 更新成绩（假设找到的学号为202003005101，课程号为C001）
UPDATE Reports SET Grade = 76 WHERE Sno = '202003005101' AND Cno = 'C001' AND Grade IS NULL;

-- 查询学分表结果
SELECT SC.*, S.Sname 
FROM StuCredits SC 
JOIN Students S ON SC.Sno = S.Sno 
WHERE SC.Sno = '202003005101';

-- （2）修改该学生另一门课程的成绩
-- 查询该学生其他课程
SELECT R.Sno, R.Cno, C.Cname, R.Grade
FROM Reports R
JOIN Courses C ON R.Cno = C.Cno
WHERE R.Sno = '202003005101';

-- 更新另一门成绩（假设课程号为C002）
UPDATE Reports SET Grade = 85 WHERE Sno = '202003005101' AND Cno = 'C002';

-- 查询学分表结果
SELECT SC.*, S.Sname 
FROM StuCredits SC 
JOIN Students S ON SC.Sno = S.Sno 
WHERE SC.Sno = '202003005101';
扫描二维码，在手机上阅读
收藏
下一篇 【MySQL】实验07 数据库的安全性保护
powered by emlog pro
服务器供应商 新区云数据

友情链接
白衣Ink


sitemap