数据库触发器

最新推荐文章于 2025-11-10 16:07:00 发布

原创最新推荐文章于 2025-11-10 16:07:00 发布 · 730 阅读

13 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#数据库

本文详细介绍了如何在SQL中创建触发器，包括在Students、Teachers和Courses表上的BEFORE和AFTERUPDATE/INSERT/DELETE触发器，以及它们的应用场景，如检查学生年龄、职称晋升和学分管理。

创建触发器的SOL语句
CREATE TRIGGER <触发器名>
BEFORE或AFTER
[INSERT] [,UPDATE] [DELETE]
ON <表名>
FOR EACH ROW
SOL语句 [,...n]

DROP TRIGGER IF EXISTS Trig_UpdRepGrade;

例题：

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

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 ;


#验证
UPDATE Students

SET Sbirth='1996-03-22'

WHERE Sno='2014112103';

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

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;

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

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 ;

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

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 ;


#验证
NSERT INTO Couses

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

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

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

（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 ;