How to create a trigger for gpa or cgpa?
<p>Hi i am working on a college result system where there are various semesters. I have a table structure like this:-</p>

<pre><code>+------+------+------+------+------+------+------+------+------+
| sem1 | sem2 | sem3 | sem4 | sem5 | sem6 | sem7 | sem8 | cgpa |
+------+------+------+------+------+------+------+------+------+
|      |      |      |      |      |      |      |      |      |
+------+------+------+------+------+------+------+------+------+
</code></pre>

<p>I want to design a trigger so that cgpa column is automatically calculated by the values in the semester For ex. when there is a value only in sem 1 then cgpa=sem1 and if value of sem1 and sem2 is not null then cgpa = (sem1 +sem2)/2 and if value of sem1,sem2 and sem3 is not null then cgpa= (sem1 + sem2 + sem3)/3 and so on for other conditions.</p>

<p>I am new to the concept of triggers and unable think of the right way to do it. The below given code is what i tried and i know this doesn't work:-</p>

<pre><code>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
</code></pre>

<p>I am new to triggers so I am unable to think of a way to do it. I would be highly grateful if anyone can help me in doing this. Any help is highly appreciated.</p>