在PL/SQL中创建一个自动分配等级的触发器

Question

我想做一个触发器，当我在一个表中更新学生的标记…然后它会自动分配一个分数。

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

但它发现它错误…如何修复

Answer 1

对于这个用例，我建议使用虚拟列。您将节省空间(仅存储有关列的元数据)，并且等级将始终保持同步(有人可以暂时禁用触发器，这可能会导致使用基于触发器的方法的数据完整性问题)。表上的DML也会更快。

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

Answer 2

您无法更新触发触发器的表，您已经在更新(或插入)它的过程中，所以我猜您得到的是"ORA-04091: table is mutating";例外。如果没有，你会的。您可能会得到编译错误。但更新是不必要的。而不是尝试更新，只是使用赋值。对New和/或Old的引用也需要一个前导冒号(:)。

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 ;

Answer 3

我建议在触发前使用:

CREATE OR ALTER TRIGGER calculate_grade
BEFORE INSERT OR UPDATE ON student_subject
BEGIN
SELECT (CASE WHEN (:new.firstterm + :new.secondterm) >= 80 THEN 'A'
WHEN (:new.firstterm + :new.secondterm) >= 65 THEN 'B'
WHEN (:new.firstterm + :new.secondterm) >= 50 THEN 'C'
WHEN (:new.firstterm + :new.secondterm) >= 40 THEN 'D'
WHEN (:new.firstterm + :new.secondterm) >= 20 THEN 'E'
ELSE 'F'
END)
INTO :NEW.GRADE
FROM DUAL;
END;

请注意，CASE逻辑是按顺序求值的，因此您不必对条件使用BETWEEN。