创建触发器的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 ;