Postgresql Trigger based on value of one column to change or update value of other column in each row. [closed]

Question

I am a newbie student in Postgresql. I have only 4 columns in students table

id              serial,
student_name    varchar(50), 
student_marks   integer,
grade           varchar(1)

Now I wish to create a trigger (Language : pl/sql) named fill_grade_auto so that depending on student_marks, the grade will be automatically updated as A (for above 90 marks) , B (for above 60 but less than 90) and rest with a C grade.

This trigger should be fired on every after insert or update on each row. I have tried different methods, but with no success.

Answer 1

You can do it this way

CREATE OR REPLACE FUNCTION set_grade() 
RETURNS TRIGGER 
AS $$
BEGIN
  NEW.grade := CASE WHEN NEW.student_marks >= 90 THEN 'A' 
                    WHEN NEW.student_marks BETWEEN 60 AND 89 THEN 'B' 
                    ELSE 'C' END;

  RETURN NEW;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER set_grade_trigger 
BEFORE INSERT OR UPDATE ON students 
FOR EACH ROW 
  EXECUTE PROCEDURE set_grade();

Note: You may need to adjust boundaries for grades in CASE, because your question is a bit ambiguous on the subject. If read word for word a value of 90 doesn't belong to any grade. Therefore I took the liberty to interpret it my way (A: 90<=marks, B: 60<= marks<90, c: marks<60)

Here is SQLFiddle demo