I am a beginner and studying about trigger to calculate grade of students from this link. Student_Marks Trigger The query for before update trigger works fine. I was trying to change the trigger a little bit. I want to calculate the Grade after inserting marks of a student. Hence, after trigger instead of update. I got stuck.
My query is as follows:
DELIMITER
$$
CREATE TRIGGER `student_marks`
AFTER INSERT
ON student FOR EACH ROW
BEGIN
SET NEW.Total =NEW.SUB1+NEW.SUB2+NEW.SUB3+NEW.SUB4+NEW.SUB5;
SET NEW.Percentage = NEW.Total/5;
IF NEW.Percentage >=90 THEN
SET NEW.Grade = 'EXCELLENT';
ELSEIF NEW.Percentage>=75 AND NEW.Percentage<90 THEN
SET NEW.Grade = 'VERY GOOD';
ELSEIF NEW.Percentage>=60 AND NEW.Percentage<75 THEN
SET NEW.Grade = 'GOOD';
ELSEIF NEW.Percentage>=40 AND NEW.Percentage<60 THEN
SET NEW.Grade = 'AVERAGE';
ELSE SET NEW.Grade = 'NOT PROMOTED';
END IF;
END;
$$
You have to use BEFORE INSERT
instead of AFTER INSERT
to save the calculated value of the new row. You can't affect the row AFTER INSERT
because the row is already saved.
DELIMITER $$
CREATE TRIGGER `student_marks`
BEFORE INSERT ON student_marks FOR EACH ROW
BEGIN
SET NEW.Total =NEW.SUB1+NEW.SUB2+NEW.SUB3+NEW.SUB4+NEW.SUB5;
SET NEW.Percentage = NEW.Total/5;
IF NEW.Percentage >=90 THEN
SET NEW.Grade = 'EXCELLENT';
ELSEIF NEW.Percentage>=75 AND NEW.Percentage<90 THEN
SET NEW.Grade = 'VERY GOOD';
ELSEIF NEW.Percentage>=60 AND NEW.Percentage<75 THEN
SET NEW.Grade = 'GOOD';
ELSEIF NEW.Percentage>=40 AND NEW.Percentage<60 THEN
SET NEW.Grade = 'AVERAGE';
ELSE
SET NEW.Grade = 'NOT PROMOTED';
END IF;
END;
$$
You also need to change the table name for the TRIGGER
(use INSERT ON student_marks
instead of INSERT ON student
).