Create a trigger that automatically assign grade in PL/SQL

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 asked Jan 28, 2021 at 23:42 Muhammad Nur Addin Muhammad Nur Addin What kind of error are you facing ? Put the error please. Commented Jan 28, 2021 at 23:49

3 Answers 3

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 )

answered Jan 29, 2021 at 7:48 540 1 1 gold badge 3 3 silver badges 9 9 bronze badges

You cannot Update the table that fired the trigger, you are already in the process of updating (or inserting) it so I'm guessing you getting a "ORA-04091: table is mutating" exception. If not you will. You could be getting compile errors. But updating is not necessary. Instead of attempting update just use assignment. Also references to New and/or Old require a leading colon (:).

create or alter trigger calculate_grade after insert or update on student_subject begin :new.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 ;