嗨,我正在研究一个有不同学期的大学成绩系统。我有这样的表结构:
+------+------+------+------+------+------+------+------+------+
| sem1 | sem2 | sem3 | sem4 | sem5 | sem6 | sem7 | sem8 | cgpa |
+------+------+------+------+------+------+------+------+------+
| | | | | | | | | |
+------+------+------+------+------+------+------+------+------+
我想设计一个触发器,以便cgpa列由学期For ex的值自动计算。当仅在sem 1中存在一个值时,则cgpa = sem1;如果sem1和sem2的值不为null,则cgpa =(sem1 + sem2)/ 2;如果sem1,sem2和sem3的值不为null,则cgpa =(sem1 + sem2 + sem3)/ 3,以此类推。
我对触发器的概念是陌生的,无法想到正确的方法。下面给出的代码是我尝试过的,我知道这行不通:-
CREATE DEFINER=`root`@`localhost` TRIGGER `ABC` BEFORE UPDATE ON `cgpa` FOR EACH ROW BEGIN
SET
NEW.cgpa = NEW.sem1;
SET
NEW.cgpa = (NEW.sem1 + NEW.sem2) / 2;
SET
NEW.cgpa = (NEW.sem1 + NEW.sem2 + NEW.sem3) / 3;
SET
NEW.cgpa = (NEW.sem1 + NEW.sem2 + NEW.sem3 + NEW.sem4) / 4;
SET
NEW.cgpa = (NEW.sem1 + NEW.sem2 + NEW.sem3 + NEW.sem4 + NEW.sem5) / 5;
SET
NEW.cgpa = (NEW.sem1 + NEW.sem2 + NEW.sem3 + NEW.sem4 + NEW.sem5 + NEW.sem6) / 6;
SET
NEW.cgpa = (NEW.sem1 + NEW.sem2 + NEW.sem3 + NEW.sem4 + NEW.sem5 + NEW.sem6 + NEW.sem7) / 7;
SET
NEW.cgpa = (NEW.sem1 + NEW.sem2 + NEW.sem3 + NEW.sem4 + NEW.sem5 + NEW.sem6 + NEW.sem7 + NEW.sem8) / 8;
END
我是触发器的新手,所以我想不出办法。如果有人可以帮助我,我将不胜感激。非常感谢您的帮助。
最佳答案
尝试将sem1,sem2,sem3,sem4,sem5值初始化为0,并在所有情况下求和