Trigger to adding an Percentage & Grade

Reference:

1.   http://www.w3resource.com/mysql/mysql-triggers.php

MySQL Trigger : Example BEFORE UPDATE

We have a table student_marks with 10 columns and 4 rows. There are data only in STUDENT_ID and NAME columns.

mysql> SELECT * FROM STUDENT_MARKS;
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
| STUDENT_ID | NAME             | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
|          1 | Steven King      |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          2 | Neena  Kochhar   |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          3 | Lex  De Haan     |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          4 | Alexander Hunold |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
4 rows in set (0.00 sec)

Now the exam is over and we have received all subject marks, now we will update the table, total marks of all subject, percentage of total marks and grade will be automatically calculated. For this sample calculation, the following conditions are assumed :

Total Marks (will be stored in TOTAL column) : TOTAL = SUB1 + SUB2 + SUB3 + SUB4 + SUB5

Percentage of Marks (will be stored in PER_MARKS column) : PER_MARKS = (TOTAL)/5

Grade (will be stored GRADE column) : 
- If PER_MARKS>=90 -> 'EXCELLENT'
- If PER_MARKS>=75 AND PER_MARKS<90 -> 'VERY GOOD'
- If PER_MARKS>=60 AND PER_MARKS<75 -> 'GOOD'
- If PER_MARKS>=40 AND PER_MARKS<60 -> 'AVERAGE'
- If PER_MARKS<40-> 'NOT PROMOTED'

Here is the code :

mysql> UPDATE STUDENT_MARKS SET SUB1 = 54, SUB2 = 69, SUB3 = 89, SUB4 = 87, SUB5 = 59 WHERE STUDENT_ID = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  
Changed: 1  
Warnings: 0

Let update the marks of a student :

USE `test`;
DELIMITER 
$$
CREATE TRIGGER `student_marks_BUPD` 
BEFORE UPDATE 
ON student_marks FOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
BEGIN 
SET NEW.TOTAL = NEW.SUB1 + NEW.SUB2 + NEW.SUB3 + NEW.SUB4 + NEW.SUB5; 
SET NEW.PER_MARKS = NEW.TOTAL/5;
IF NEW.PER_MARKS >=90 THEN
SET NEW.GRADE = 'EXCELLENT';
ELSEIF NEW.PER_MARKS>=75 AND NEW.PER_MARKS<90 THEN
SET NEW.GRADE = 'VERY GOOD';
ELSEIF NEW.PER_MARKS>=60 AND NEW.PER_MARKS<75 THEN
SET NEW.GRADE = 'GOOD';
ELSEIF NEW.PER_MARKS>=40 AND NEW.PER_MARKS<60 THEN
SET NEW.GRADE = 'AVERAGE';
ELSESET NEW.GRADE = 'NOT PROMOTED';
END IF;
END;
$$

Now check the STUDENT_MARKS table with updated data. The trigger show you the updated records in 'stu_log'.

mysql> SELECT * FROM STUDENT_MARKS;
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
| STUDENT_ID | NAME             | SUB1 | SUB2 | SUB3 | SUB4 | SUB5 | TOTAL | PER_MARKS | GRADE |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
|          1 | Steven King      |   54 |   69 |   89 |   87 |   59 |   358 |     71.60 | GOOD  |
|          2 | Neena  Kochhar   |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          3 | Lex  De Haan     |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
|          4 | Alexander Hunold |    0 |    0 |    0 |    0 |    0 |     0 |      0.00 |       |
+------------+------------------+------+------+------+------+------+-------+-----------+-------+
4 rows in set (0.00 sec)