一篇文章带你深入了解Mysql触发器
作者:zsr6135 时间:2024-01-14 15:42:35
基于school数据库中的student, course, sc表,创建满足下述要求的触发器:
数据库navicat
cmd界面运行
1.对SC表进行插入或修改时,如果考试成绩不在0-100范围内时,则撤销插入或修改操作。
DELIMITER ;
DROP TRIGGER IF EXISTS `tri_update_grade`;
DELIMITER ;;
CREATE TRIGGER `tri_update_grade` BEFORE UPDATE ON `sc` FOR EACH ROW begin
if new.grade > 100 then
delete from sc;
elseif new.grade < 0 then
delete from sc;
end if;
end
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `tri_insert_grade`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_grade` BEFORE INSERT ON `sc` FOR EACH ROW begin
if new.grade > 100 then
delete from sc;
elseif new.grade < 0 then
delete from sc;
end if;
end
;;
2.对SC表进行插入时,如果学生的选课总学分超过30,则报错并撤销插入。
DELIMITER ;
DROP TRIGGER IF EXISTS `tri_insert_credit`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_credit` BEFORE INSERT ON `sc` FOR EACH ROW begin
SELECT sum(Ccredit) INTO @sumcred from Student join SC on SC.Sno=Student.Sno join Course on Course.Cno=SC.Cno WHERE sc.Sno=new.Sno;
if (@sumcred>30)
then
delete from sc;
end if;
end
;;
3.对SC表进行修改时,不允许修改考试成绩不及格的学生选课记录。
DELIMITER ;
DROP TRIGGER IF EXISTS `tri_grade`;
DELIMITER ;;
CREATE TRIGGER `tri_grade` BEFORE UPDATE ON `sc` FOR EACH ROW begin
select grade INTO @gra from sc where sno=new.sno and sno=new.sno;
if (@gra >= 60)
then
set new.grade=20;
else
delete from sc;
end if;
end
;;
4.对STUDENT表进行插入时,如果年龄为空,则将该生年龄设置为所有学生年龄的平均值。
DELIMITER ;
DROP TRIGGER IF EXISTS `tri_student`;
DELIMITER ;;
CREATE TRIGGER `tri_student` BEFORE INSERT ON `student` FOR EACH ROW begin
select avg(Sage) INTO @avgage from student;
SET @old_age = new.Sage;
if @old_age IS NULL
then
set new.Sage=@avgage;
end if;
end
;;
5.在STUDENT表中增加两列,分别用来存放学生的平均成绩和等级,并对SC表进行插入后,自动维护学生的平均成绩和等级(85-100,等级为“优"; 60-85,等级为“良”,60 以下,等级为“差")
DELIMITER ;
DROP TRIGGER IF EXISTS `tri_comment`;
DELIMITER ;;
CREATE TRIGGER `tri_comment` BEFORE UPDATE ON `student` FOR EACH ROW begin
select avg(Grade) INTO @avggra from sc where sno=new.sno;
if @avggra > 85 then
update sc set new.Savg=@avggra,new.Scomment="you";
else if @avggra > 60 and @avggra <= 85 then
update sc set new.Savg=@avggra,new.Scomment="you";
else
update sc set new.Savg=@avggra,new.Scomment='cha';
end if;
end if;
end
;;
DELIMITER ;
来源:https://blog.csdn.net/zsr6135/article/details/117261567
标签:Mysql,触发器
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
golang 字符串比较是否相等的方法示例
2024-02-05 14:45:07
Javascript实现通过选择周数显示开始日和结束日的实现代码
2023-08-23 13:44:52
ASP生成柱型体,折线图,饼图源代码
2007-09-20 12:56:00
用asp实现读取文件的最后一行的代码
2011-03-08 10:55:00
MySQL8自增主键变化图文详解
2024-01-25 19:08:38
![](https://img.aspxhome.com/file/2023/6/71786_0s.png)
网页设计十大诀窍
2007-10-19 13:03:00
对python中assert、isinstance的用法详解
2022-04-29 14:10:54
Python中排序函数sorted()函数的使用实例
2021-08-01 09:52:02
![](https://img.aspxhome.com/file/2023/5/117465_0s.jpg)
Python 分支结构详解
2021-03-17 01:43:06
![](https://img.aspxhome.com/file/2023/4/72084_0s.png)
验证码-挑战你的智慧
2008-09-10 13:08:00
![](https://img.aspxhome.com/file/UploadPic/20089/10/2008910131428605s.jpg)
Pygame Draw绘图函数的具体使用
2021-05-24 12:14:49
![](https://img.aspxhome.com/file/2023/9/128589_0s.gif)
Python新手如何理解循环加载模块
2022-10-09 00:12:53
使用k8s部署Django项目的方法步骤
2022-12-30 01:59:56
HTML 5 V.S. XHTML 2:HTML5倍受青睐,XHTML2处境尴尬
2009-06-08 12:57:00
详解MySQL数据库之更新语句
2010-08-08 09:15:00
python使用time、datetime返回工作日列表实例代码
2022-03-21 14:02:15
基于JS实现01支付后的10秒倒计时
2024-04-10 11:02:30
![](https://img.aspxhome.com/file/2023/8/136808_0s.png)
Javascript 逗号“,”引发的血案
2008-08-20 18:37:00
python 常见的排序算法实现汇总
2023-09-17 13:47:24
![](https://img.aspxhome.com/file/2023/9/131459_0s.png)
oracle 函数
2010-07-23 13:06:00