oracle - 变异表 ORA-04091 oracle（试图拒绝更新条件）

Question

我正在尝试重写触发器，因此它不会给出变异表 (ORA-04091) 错误。表和触发器定义如下（注释部分触发器给出变异表异常）

CREATE TABLE "COPYREAL"."PL_EDUCPLANS" 
   (    "PLANID" NUMBER(10,0), 
    "STUDYFORM" NUMBER(2,0) NOT NULL ENABLE, 
    "SKILLCODE" NUMBER(2,0) NOT NULL ENABLE, 
    "YEARBEGIN" NUMBER(4,0) NOT NULL ENABLE, 
    "SPECCODE" VARCHAR2(10 CHAR) NOT NULL ENABLE, 
    "SEMESTERS" NUMBER(2,0) NOT NULL ENABLE, 
    "BIFURCATE_SEMESTER" NUMBER(2,0), 
    "CHAIRID" NUMBER(4,0), 
    "LESS10" NUMBER(1,0), 
    "CURATOR_CHAIR" NUMBER(4,0), 
    "SCHOOL_DISCS" NUMBER(1,0), 
    "MARKSYSTEMID" NUMBER(4,0) NOT NULL ENABLE, 
    "SKILL" VARCHAR2(50 CHAR), 
    "VKR_WEEKS" NUMBER(2,0), 
    "TOTALHOURS_GOS" NUMBER(5,0), 
    "NORM_LEARN_TIME" NUMBER(3,0), 
    "SKILL_ENG" VARCHAR2(50 CHAR), 
    "FIS_ITEM_UID" NUMBER(6,0), 
    "TOTALCOST" NUMBER(10,0), 
    "TOTALCOST_STR" VARCHAR2(800 CHAR), 
    "FORWP" NUMBER(1,0) DEFAULT 0 NOT NULL ENABLE, 
    "ISOLD" NUMBER(1,0), 
    "COMMENTARY" VARCHAR2(500 CHAR), 
     CONSTRAINT "PK_PL_EDUCPLANS" PRIMARY KEY ("PLANID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE, 
     CONSTRAINT "FK_PL_EDUCPLANS_CHAIRID" FOREIGN KEY ("CHAIRID")
      REFERENCES "COPYREAL"."RB_DEPARTMENTS" ("CODE") ENABLE, 
     CONSTRAINT "FK_CURATORCHAIR_DEP" FOREIGN KEY ("CURATOR_CHAIR")
      REFERENCES "COPYREAL"."RB_DEPARTMENTS" ("CODE") ENABLE, 
     CONSTRAINT "FK_PL_EDUCPLANS_RB_SKILLS" FOREIGN KEY ("SKILLCODE")
      REFERENCES "COPYREAL"."RB_SKILLS" ("CODE") ENABLE, 
     CONSTRAINT "FK_PL_EDUCPLANS_RB_SPECS" FOREIGN KEY ("SPECCODE")
      REFERENCES "COPYREAL"."RB_SPECIALITY" ("CODE") ENABLE, 
     CONSTRAINT "FK_PL_EDUCPLANS_RB_STUDYFORMS" FOREIGN KEY ("STUDYFORM")
      REFERENCES "COPYREAL"."RB_STUDYFORMS" ("CODE") ENABLE, 
     CONSTRAINT "FK_PLEDUCPLANS_SCMARKSYSTEMS" FOREIGN KEY ("MARKSYSTEMID")
      REFERENCES "COPYREAL"."SC_MARKSYSTEMS" ("MARKSYSTEMID") ENABLE
   );

触发器是：

create or replace TRIGGER "COPYREAL".tr_pl_educplans
before insert or update or delete
on pl_educPlans
for each row
DECLARE
   l_flag NUMBER(1);
begin
 IF :NEW.FORWP = 0 THEN
  if inserting or updating then 

  delete from pl_processed_plans where planid=:new.planid;

   DELETE FROM PL_PROCESSED_PLANS_B WHERE PLANID=:NEW.PLANID;

  /* select case when exists(
       SELECT 1 
        FROM PL_EDUCPLANS EP 
                where EP.FORWP=0 AND EP.YEARBEGIN=:NEW.YEARBEGIN AND EP.STUDYFORM=:NEW.STUDYFORM AND ep.SKILLCODE=:NEW.SKILLCODE and ep.SPECCODE=:NEW.SPECCODE
     ) then 1 else 0 end
     into l_flag from Dual;

     IF L_FLAG = 1 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Plan exists!');
     end if;
  */

  end if;

  if deleting then
     delete from pl_processed_plans where planid=:old.planid;

     delete from pl_processed_plans_b where planid=:old.planid;

     delete from pl_plan_activities_mt where planid=:old.planid;

     delete from pl_plan_activities_b where planid=:old.planid;
  END IF;
   end if;
end;

insert 按原样运行（在尝试为常规计划插入重复值时给出异常“计划存在”），即一旦您在表上运行更新（显然您不能从触发触发器的同一表中选择）第一名）

基本上我在这里想要实现的是强制执行以下业务逻辑，你不能有 2 个常规计划（当它的 forwp 属性值为 0 时，该计划被认为是常规计划））具有相同的学习表格、技能代码、规范代码和 yearbegin 值彼此之间，您可以拥有尽可能多的不规则计划，这些计划彼此具有相同的学习形式、技能代码、规范代码和年初值。一个不规则的计划（当它的 forwp 属性值为 1 时，该计划被认为是不规则的）在引入 forwp 属性之前之前的业务逻辑是由 pl_eduplans 表上的唯一约束（studyform,skillcode,speccode,yearbegin）强制执行的，我'我不太确定如何执行规则现在我已经阅读了给出的建议https://oracle-base.com/articles/9i/mutating-table-exceptions但我不确定如何将它们应用于我的案例

是否可以通过触发器强制执行此类业务逻辑规则？还是应该在应用程序级别进行此类检查？

标签： oracletriggers