一篇文章帶你深入了解Mysql觸發(fā)器
發(fā)布日期：2022-02-09 08:12 | 文章來源：源碼中國
基于school數(shù)據(jù)庫中的student, course, sc表,創(chuàng)建滿足下述要求的觸發(fā)器:
數(shù)據(jù)庫navicat cmd界面運(yùn)行
1.對SC表進(jìn)行插入或修改時，如果考試成績不在0-100范圍內(nèi)時，則撤銷插入或修改操作。
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表進(jìn)行插入時，如果學(xué)生的選課總學(xué)分超過30,則報(bào)錯并撤銷插入。
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表進(jìn)行修改時，不允許修改考試成績不及格的學(xué)生選課記錄。
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表進(jìn)行插入時，如果年齡為空，則將該生年齡設(shè)置為所有學(xué)生年齡的平均值。
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表中增加兩列，分別用來存放學(xué)生的平均成績和等級，并對SC表進(jìn)行插入后,自動維護(hù)學(xué)生的平均成績和等級(85-100,等級為“優(yōu)"; 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 ;
總結(jié)
本篇文章就到這里了，希望能給你帶來幫助，也希望您能夠多多關(guān)注本站的更多內(nèi)容！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信