oracle - Create a trigger that automatically assign grade in PL/SQL

Question

I want to make a trigger that when I update student's mark in a table.. then it will automatically assign a grade.

CREATE OR ALTER TRIGGER calculate_grade
AFTER INSERT OR UPDATE ON student_subject
BEGIN
    UPDATE student_subject SET grade =
    CASE
        WHEN (new.firstterm+new.secondterm) >= 80 THEN 'A'
        WHEN (new.firstterm+new.secondterm) >= 65 AND (new.firstterm+new.secondterm) < 80 THEN 'B'
        WHEN (new.firstterm+new.secondterm) >= 50 AND (new.firstterm+new.secondterm) < 65 THEN 'C'
        WHEN (new.firstterm+new.secondterm) >= 40 AND (new.firstterm+new.secondterm) < 50 THEN 'D'
        WHEN (new.firstterm+new.secondterm) >= 20 AND (new.firstterm+new.secondterm) < 40 THEN 'E'
        ELSE 'F'
    END
END

but it found it errors.. how do I fix it

question from:https://stackoverflow.com/questions/65946818/create-a-trigger-that-automatically-assign-grade-in-pl-sql

Answer 1

For this use case I would recommend using virtual column. You will save on space (only metadata are stored about the column) and grade will be always in sync (someone can disable trigger for a while and this could cause problems with data integrity using trigger based approach). DML on the table will be also faster.

create table t(
  id number primary key,
  firstterm number,
  secondterm number,
  grade char(1) generated always as (
        case
            when firstterm + secondterm >= 80 then 'A'
            when firstterm + secondterm >= 65 then 'B'
            when firstterm + secondterm >= 50 then 'C'
            when firstterm + secondterm >= 40 then 'D'
            when firstterm + secondterm >= 20 then 'E'
            when firstterm + secondterm >= 0  then 'F'
            else null
        end
  ) virtual
)

sqlfiddle