触发器个人复习用

互评批阅

    实验06 触发器

    互评指标：

（1）请按照答案中给的参考答案进行评分，若有扣分，必须在评语中说明扣分原
因； （2）本组题目中的正反例SQL语句及截图，分别为1分，其它分数可依据情况
进行区分。

    一. 填空题（共1题）

      1. (填空题) 请输入你的学号最后两位____。

/***的答案：/
    (1) 41

/正确答案：/
    (1) 01-99

    二. 简答题（共15题）

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

/***的答案：/

    掌握触发器的创建、修改、删除及其使用方法

    掌握触发器的功能

/正确答案：/

    (1)掌握触发器的创建、修改、删除及其使用方法
    (2)掌握触发器的功能

评分 分
评语
段落格式
字体
字号
点击上传
x
        

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

/***的答案：/

    create trigger Trig_UpdStuBirth_41
      before update
      on students41
      for each row
      begin
          declare msg varchar(200);
          if(left(NEW.Sno,4)-year(new.Sbirth) not between 14 and 40) then
              set msg = '学生的出生日期有误，请确认后重新输入！';
              signal sqlstate 'HY000' SET MESSAGE_TEXT = msg;
          end if;
      end;

/正确答案：/

    MySQL程序

    DELIMITER //

    CREATE TRIGGER Trig_UpdStuBirth

    BEFORE UPDATE

    ON Students

    FOR EACH ROW

    BEGIN

        DECLARE msg VARCHAR(200);

        IF (NEW.Sbirth!=OLD.Sbirth) THEN

            IF ( (CONVERT(LEFT(NEW.Sno,4),UNSIGNED)-YEAR(NEW.Sbirth))
    NOT BETWEEN 14 AND 40)

            THEN

                SET msg='学生的出生日期有误,请确认后重新输入!';

                SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;

            END IF;

        END IF;

    END //

    DELIMITER ;

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      3. (简答题)

      针对UPDATE触发器Trig_UpdStuBirth_学号最后两位，分别设计并写出正反
      例的SQL语句各一条，即一条语句执行后允许更改，一条语句执行后，系统
      提示出错。要求将执行结果截图，截图中写入学号后两位。

      提示：执行正反例程序时，最好给出语句执行前后的结果对照

/***的答案：/

    反例：

    update students41 s
    set s.Sbirth = '2001-03-20'
    where s.Sname = '张建国';

    正例：

    update students41 s
    set s.Sbirth = '2000-03-20'
    where s.Sname = '张建国';

    原：

    后：

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里只给出了执行后的结果，请大家按照要求给出执行前后的结果对照，方
    便评分的同学判分

    若有人只给出了执行后的结果，则整题扣1分即可。

    （1）正例

    UPDATE Students

    SET Sbirth='1996-03-22'

    WHERE Sno='2014112103';

    （2）反例

    UPDATE Students

    SET Sbirth='2006-01-10'

    WHERE Sno='2014112104';

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      4. (简答题)

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

      提示：可以使用正则表达式来判断输入的学号是否都是数字。

/***的答案：/

    create trigger Trig_InsStuSno_41
       before insert
       on students41
       for each row
       begin
           declare msg varchar(200);
           if(length(NEW.Sno) = 10) then
               if(select NEW.Sno regexp '[^0-9]' = 1) then
                   set msg = '输入的学号不全是数字，请确认后重新输入！';
                   signal sqlstate 'HY000' set message_text = msg;
               end if;
           else
               set msg = '输入的学号长度不为10，请确认后重新输入！';
               signal sqlstate 'HY000' set message_text = msg;
           end if;
       end;

/正确答案：/

    CREATE TRIGGER Trig_InsStuSno

    BEFORE INSERT

    ON Students

    FOR EATCH ROW

    BEGIN

    DECLARE msg VARCHAR(200);

            IF LENGTH(NEW.Sno)!=10 THEN

                SET msg='学号的长度必须为10个数字!';

                SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;

            ELSEIF NOT NEW.Sno REGEXP '^[0-9]+$' THEN

        SET msg='学号必须全部为数字！';

                SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT=msg;

    END IF;

    END;

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      5. (简答题)

      针对INSERT触发器Trig_InsStuSno_学号最后两位，分别设计并写出正例的
      SQL语句一条，反例的SQL语句两条（分别对应两个判断），即正例语句执行
      后允许插入，反例语句执行后，系统提示出错。要求将执行结果截图，截图
      中写入学号后两位。

      提示：执行正反例程序时，最好给出语句执行前后的结果对照

/***的答案：/

    反例：

    长度不为10： insert into students41(Sno,Sname) values ('123456789','
    李四');

    输入的学号不全是数字： insert into students41(Sno,Sname) values
    ('123456789m','李四');

    正例：

    insert into students41(Sno,Sname) values ('1234567890','李四');

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里只给出了执行后的结果，请大家按照要求给出执行前后的结果对照，方
    便评分的同学判分

    若有人只给出了执行后的结果，则整题扣1分即可。

    （1）正例

    INSERT INTO Students(Sno,Sname)

    VALUES('2017115102','赵四');

    （2）反例

    INSERT INTO Students(Sno,Sname)

    VALUES('201711510','赵四');

    INSERT INTO Students(Sno,Sname)

    VALUES('201711510z','赵四');

评分 分
评语
段落格式
字体
字号
点击上传
x
        

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

/***的答案：/

    create trigger Trig_UpdTeaProf_41
       before update
       on teachers41
       for each row
       begin
           declare msg varchar(200);
           if(NEW.Tprof not in('助教', '讲师', '副教授', '教授')) then
               set msg = '更新的职称不正确，请确认后重新输入！';
               signal sqlstate 'HY000' set message_text = msg;
           elseif(NEW.Tprof = '讲师') then
               if(OLD.Tprof != '助教') then
                   set msg = '不允许越级晋升或降级，请确认后重新输入！';
                   signal sqlstate 'HY000' set message_text = msg;
               else
                   set NEW.Tcomm := OLD.Tcomm + 300;
               end if;
           elseif(NEW.Tprof = '副教授') then
               if(OLD.Tprof != '讲师') then
                   set msg = '不允许越级晋升或降级，请确认后重新输入！';
                   signal sqlstate 'HY000' set message_text = msg;
               else
                   set NEW.Tcomm := OLD.Tcomm + 500;
               end if;
           elseif(NEW.Tprof = '教授') then
               if(OLD.Tprof != '副教授') then
                   set msg = '不允许越级晋升或降级，请确认后重新输入！';
                   signal sqlstate 'HY000' set message_text = msg;
               else
                   set NEW.Tcomm := OLD.Tcomm + 900;
               end if;
           end if;
       end;

/正确答案：/

    DELIMITER //

    CREATE TRIGGER Trig_UpdTeaProf

    BEFORE UPDATE

    ON Teachers

    FOR EACH ROW

    BEGIN

        DECLARE msg VARCHAR(200);

        IF (NEW.Tprof!=OLD.Tprof) THEN

            IF (NEW.Tprof NOT IN ('助教','讲师','副教授','教授')) THEN

                SET msg='教师职称有误！';

                SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT =msg;

            ELSEIF (OLD.Tprof='助教' AND NEW.Tprof='讲师') THEN

                SET NEW.Tcomm=NEW.Tcomm+300;

            ELSEIF (OLD.Tprof='讲师' AND NEW.Tprof='副教授') THEN

                SET NEW.Tcomm=NEW.Tcomm+500;

            ELSEIF (OLD.Tprof='副教授' AND NEW.Tprof='教授') THEN

                SET NEW.Tcomm=NEW.Tcomm+900;

            ELSE

                SET msg='教师职称不允许越级晋升或降级!';

                SIGNAL SQLSTATE 'HY001' SET MESSAGE_TEXT=msg;

            END IF;

        END IF;

    END //

    DELIMITER ;

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      7. (简答题)

      针对UPDATE触发器Trig_UpdTeaTpro_学号最后两位，设计并写出反例的SQL
      语句两条，如修改的职称写成了'jiangshi'，原有的职称'助教'改成了'副
      教授'，语句执行后，系统提示出错信息。要求将执行结果截图，截图中写
      入学号后两位。

      提示：执行正反例程序时，最好给出语句执行前后的结果对照

/***的答案：/

    原：

    一： update teachers41
    set Tprof = 'jiangshi'
    where Tno = 'T010';

    二：update teachers41
    set Tprof = '副教授'
    where Tno = 'T010';

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里只给出了执行后的结果，请大家按照要求给出执行前后的结果对照，方
    便评分的同学判分

    若有人只给出了执行后的结果，则整题扣1分即可。

    反例1：

    UPDATE teachers

    SET Tprof='jiangshi'

    WHERE Tno='T010';

    反例2：

    UPDATE teachers

    SET Tprof='副教授'

    WHERE Tno='T010';

评分 分
评语
段落格式
字体
字号
点击上传
x
        

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

/***的答案：/

    原：

    语句：update teachers41

    set Tprof = '副教授'
    where Tno = 'T003';

    原因：教师表 teachers41中有check约束：

    若教师的职称是副教授，则其工资要在2600元到3500元之间，工资不满足

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里只给出了执行后的结果，请大家按照要求给出执行前后的结果对照，方
    便评分的同学判分

    若有人只给出了执行后的结果，则整题扣1分即可。

    正例：

    UPDATE teachers

    SET Tprof='副教授'

    WHERE Tno='T003';

    原因：之前设置的CHECK约束，每个职称的最低工资有限制

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      9. (简答题)

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

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

/***的答案：/

    原：

    语句：create trigger Trig_UpdTeaProf_41
       before update
       on teachers41
       for each row
       begin
           declare msg varchar(200);
           if(NEW.Tprof not in('助教', '讲师', '副教授', '教授')) then
               set msg = '更新的职称不正确，请确认后重新输入！';
               signal sqlstate 'HY000' set message_text = msg;
           elseif(NEW.Tprof = '讲师') then
               if(OLD.Tprof != '助教') then
                   set msg = '不允许越级晋升或降级，请确认后重新输入！';
                   signal sqlstate 'HY000' set message_text = msg;
               else
                   set NEW.Tsal = 1600;
                   set NEW.Tcomm := OLD.Tcomm + 300;
               end if;
           elseif(NEW.Tprof = '副教授') then
               if(OLD.Tprof != '讲师') then
                   set msg = '不允许越级晋升或降级，请确认后重新输入！';
                   signal sqlstate 'HY000' set message_text = msg;
               else
                   set NEW.Tsal = 2600;
                   set NEW.Tcomm := OLD.Tcomm + 500;
               end if;
           elseif(NEW.Tprof = '教授') then
               if(OLD.Tprof != '副教授') then
                   set msg = '不允许越级晋升或降级，请确认后重新输入！';
                   signal sqlstate 'HY000' set message_text = msg;
               else
                   set NEW.Tsal = 3500;
                   set NEW.Tcomm := OLD.Tcomm + 900;
               end if;
           end if;
       end;

    update teachers41
    set Tprof = '副教授'
    where Tno = 'T003';

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里只给出了执行后的结果，请大家按照要求给出执行前后的结果对照，方
    便评分的同学判分

    若有人只给出了执行后的结果，则整题扣1分即可。

    （1）修改SQL程序：

    DELIMITER //

    DROP TRIGGER IF EXISTS Trig_UpdTeaProf;

    CREATE TRIGGER Trig_UpdTeaProf

    BEFORE UPDATE

    ON Teachers

    FOR EACH ROW

    BEGIN

        DECLARE msg VARCHAR(200);

        IF (NEW.Tprof!=OLD.Tprof) THEN

            IF (NEW.Tprof NOT IN ('助教','讲师','副教授','教授')) THEN

                SET msg='教师职称有误！';

                SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT =msg;

            ELSEIF (OLD.Tprof='助教' AND NEW.Tprof='讲师') THEN

                SET NEW.Tsal=1600;

                SET NEW.Tcomm=NEW.Tcomm+300;

            ELSEIF (OLD.Tprof='讲师' AND NEW.Tprof='副教授') THEN

                SET NEW.Tsal=2600;

                SET NEW.Tcomm=NEW.Tcomm+500;

            ELSEIF (OLD.Tprof='副教授' AND NEW.Tprof='教授') THEN

                SET NEW.Tsal=3600;

                SET NEW.Tcomm=NEW.Tcomm+900;

            ELSE

                SET msg='教师职称不允许越级晋升或降级!';

                SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT=msg;

            END IF;

        END IF;

    END //

    DELIMITER ;

    （2）正例

    UPDATE teachers

    SET Tprof='副教授'

    WHERE Tno='T004';

评分 分
评语
段落格式
字体
字号
点击上传
x
        

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

/***的答案：/

    create trigger Trig_InsTutCno_41
       before insert
       on tutors41
       for each row
       begin
           declare msg varchar(200);
           if(NEW.Cno in(select tut.Cno
                          from tutors41 tut
                          where tut.Sclass = NEW.Sclass )) then
               set msg = '已经有教师给当前班级讲授过该门课程了';
               signal sqlstate 'HY000' set message_text  = msg;
           else
               insert into reports41(sno, cno, racademicyear, rterm)
               (select s.Sno,
                       NEW.Cno,
                       new.Tacademicyear,
                       new.Tterm
                from students41 s
                where s.Sclass = NEW.Sclass
               );
           end if;
       end;

/正确答案：/

    SQL程序

    DELIMITER //

    DROP TRIGGER IF EXISTS Trig_InsTutCno;

    CREATE TRIGGER Trig_InsTutCno

    BEFORE INSERT ON Tutors

    FOR EACH ROW

    BEGIN

        DECLARE msg VARCHAR(200);

        IF EXISTS(SELECT * FROM Tutors WHERE Cno=NEW.Cno AND
    Sclass=NEW.Sclass) THEN

            SET msg='已有该班学生该门课程的授课信息,请确认课程和班级后重
    新输入!';

            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;

        ELSE

            INSERT INTO Reports(Sno, Cno, Racademicyear, Rterm)

                (SELECT Sno,NEW.Cno,NEW.Tacademicyear,NEW.Tterm

                 FROM Students WHERE Sclass=NEW.Sclass);

        END IF;

    END //

    DELIMITER ;

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      11. (简答题)

      针对INSERT触发器Trig_InsTutCno_学号最后两位，设计并写出正例的SQL语
      句一条，即正例语句执行后允许插入授课信息，且自动在Reports表中插入
      学生的选课信息；设计并写出反例的SQL语句一条，即反例语句执行后，系
      统提示出错。要求将执行结果截图，截图中写入学号后两位。

      提示：执行正反例程序时，最好给出语句执行前后的结果对照

/***的答案：/

    选课表原先：

    反例：insert into tutors41(tno, cno, tacademicyear, tterm, sclass)
    VALUES
    ('T001', '112p0054', '2023', '1', '20141151');

    正例：insert into tutors41(tno, cno, tacademicyear, tterm, sclass)
    VALUES
    ('T001', '112p0055', '2023', '1', '20141151');

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里只给出了执行后的结果，请大家按照要求给出执行前后的结果对照，方
    便评分的同学判分

    若有人只给出了执行后的结果，则整题扣1分即可。

    （1）正例

    ① INSERT INTO Tutors(Tno, Cno, Tacademicyear, Tterm, Sclass)

    VALUES('T006','112p0023',2017,2,'20152101');

    ② SELECT * FROM tutors

    WHERE Tno='T006' AND Cno='112p0023';

    ③ SELECT * FROM reports

    WHERE Cno='112p0023';

    （2）反例

    INSERT INTO Tutors(Tno, Cno, Tacademicyear, Tterm, Sclass)

    VALUES('T002','112p0024',2017,2,'20161151');

评分 分
评语
段落格式
字体
字号
点击上传
x
        

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

/***的答案：/

    create trigger Trig_DelCou_41
       before delete
       on courses41
       for each row
       begin
           declare msg varchar(200);
           if exists(select * from reports41 r where r.Cno = OLD.Cno) then
               if exists(select * from reports41 r where r.Cno = old.Cno
    and r.Grade is not null) then
                   set msg = '已有学生选修了该门课程，有且已有成绩,不能
    删除该课程';
                   signal sqlstate 'HY000' set message_text  = msg;
               end if;
           else
               delete from reports41 r where r.Cno = OLD.Cno;
               delete from tutors41 tut where tut.Cno = old.Cno;
           end if;
       end;

/正确答案：/

    SQL程序：

    DELIMITER //

    DROP TRIGGER IF EXISTS Trig_DelCou;

    CREATE TRIGGER Trig_DelCou

    BEFORE DELETE ON Courses

    FOR EACH ROW

    BEGIN

        DECLARE msg VARCHAR(200);

        IF EXISTS(SELECT * FROM reports WHERE Cno=OLD.Cno AND Grade IS
    NOT NULL) THEN

            SET msg='已有学生选修该门课程,且已获成绩,因此不能删除该门课程!';

            SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = msg;

        ELSE

            DELETE FROM reports WHERE Cno=OLD.Cno;

            DELETE FROM tutors WHERE tutors.Cno=OLD.Cno;

        END IF;

    END //

    DELIMITER ;

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      13. (简答题)

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

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

             添加授课信息：教师编号：'T005'，课程编号：'112p0068'，授课
      学年：2018，授课学期：1，授课班级：20161151

/***的答案：/

    正例：

    delete from courses41
    where Cname = '软件工程';

    反例：

    原：

    delete from courses41
    where Cno= '112p0074';

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里只给出了执行后的结果，请大家按照要求给出执行前后的结果对照，方
    便评分的同学判分

    若有人只给出了执行后的结果，则整题扣1分即可。

    （1）正例

    ① INSERT INTO Couses

    VALUES('112p0068','软件工程','112p0046',80,4);

    ② INSERT INTO Tutos

    VALUES('T005','112p0068',2018,1,'20161151');

    ③

    DELETE FROM courses

    WHERE Cno='112p0068';

    （2）反例

    DELETE FROM courses

    WHERE Cno='112p0046';

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      14. (简答题)

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

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

/***的答案：/

    一：create table StuCredits(
       Sno char(10) primary key comment '学号',
       TotalCredits tinyint comment '总学分',
       foreign key(Sno) references students41(Sno)
    )comment '学分表';

    二：create trigger Trig_UpdRepGrade_41
       after update
       on reports41
       for each row
       begin
           declare total_credits tinyint;
           set total_credits = (select sum(c.Ccredit)
                                from courses41 c ,
                                     reports41 r
                                where c.Cno = r.Cno
                                   and r.Grade >= 60
                                   and r.Sno = NEW.Sno);
           if exists(select * from stucredits s1 where s1.Sno = NEW.Sno)
    then
               update stucredits s2 set s2.TotalCredits = total_credits
    where s2.Sno = NEW.Sno;
           else
               insert into stucredits(sno, totalcredits) VALUES
    (NEW.Sno,total_credits);
           end if;
       end;

/正确答案：/

    （1）创建学分表

    CREATE TABLE StuCredits

    (Sno CHAR(10) primary key,

     TotalCredit INT,

     FOREIGN KEY (Sno) REFERENCES Students(Sno));

    （2）触发器程序

    DELIMITER //

    DROP TRIGGER IF EXISTS Trig_UpdRepGrade;

    CREATE  TRIGGER Trig_UpdRepGrade

    AFTER UPDATE

    ON Reports

    FOR EACH ROW

    BEGIN

        IF (NEW.Grade!=OLD.Grade) THEN

            IF NOT EXISTS(SELECT * FROM StuCredits WHERE Sno=NEW.Sno) THEN

                INSERT INTO StuCredits

                    SELECT Sno,SUM(Ccredit)

                    FROM Reports R,Courses C

                    WHERE R.Cno=C.Cno

                        AND Grade>=60 AND Sno=NEW.Sno;

            ELSE

                UPDATE StuCredits,

                    (SELECT Sno,SUM(Ccredit) TC

                    FROM Reports R,Courses C

                    WHERE R.Cno=C.Cno

                        AND Grade>=60 AND Sno=NEW.Sno) SC

                SET StuCredits.TotalCredit=SC.TC

                WHERE StuCredits.Sno=SC.Sno;

            END IF;

        END IF;

    END //

    DELIMITER ;

评分 分
评语
段落格式
字体
字号
点击上传
x
        

      15. (简答题)

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

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

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

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

/***的答案：/

    原：，

       

    一：update reports41
    set Grade = 76
    where Sno = '2014115111'
       and Cno = '112p0055';

    二：update reports41
    set Grade = 59
    where Sno = '2014115111'
       and Cno = '112p0054';

    原因：如果是before updat，那么更新之前做触发则起不了什么作用，相当于没有

/正确答案：/

    说明：例子中的数据可以自己设计，以下只是给出参考语句，评分的同学要根
    据答题者的实际数据进行判分

    我这里的执行结果仅做参考，请评分的同学根据做题人给出的结果做出判断。

    （1）

    UPDATE reports

    SET Grade=76

    WHERE Sno='2016112105' AND Cno='112p0021';

    （2）

    UPDATE reports

    SET Grade=45

    WHERE Sno='2016112105' AND Cno='112p0021';

评分 分
评语
段落格式
字体
字号
点击上传
x
        

一. 填空题

  * 1

二. 简答题

  * 2
  * 3
  * 4
  * 5
  * 6
  * 7
  * 8
  * 9
  * 10
  * 11
  * 12
  * 13
  * 14
  * 15
  * 16

总分：1分
客观题得分：1分
提交 <javascript:;>

<javascript:;>

提示

<javascript:;> <javascript:;>
<javascript:;>

提示

<javascript:;> <javascript:;>

提示

<javascript:;>
<javascript:;>

评分量表模板详情

确定 <javascript:;> 取消 <javascript:;>