Postgresql触发器基于一列的值来更改或更新每行中其他列的值。

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

我是Postgresql的新手学生。学生表中只有4列

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.

现在我想创建一个名为fill_grade_auto的触发器（语言：pl / sql），这样根据student_marks，等级将自动更新为A（大于90分），B（大于60但小于90）并且一个C级。

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

每次插入或更新每行后都应触发此触发器。我尝试了不同的方法，但没有成功。

1 个解决方案

#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)

注意：您可能需要在CASE中调整成绩的边界，因为您的问题在该主题上有点模棱两可。如果逐字逐句阅读，则值90不属于任何等级。因此，我冒昧地以自己的方式解释它（A：90 <=标记，B：60 <=标记<90，c：标记<60）

Here is SQLFiddle demo

这是SQLFiddle演示

#1