尝试创建我的第一个oracle触发器时出现问题

Question

我正在尝试创建一个触发器，在插入或更新一个记录时，TESTGROUP == 'HSSAG‘将为TESTGROUP 'HSBEJ’创建该记录的副本。并且当用于HSSAG的记录被更新时，相关的HSBEJ记录将被更新。

create or replace TRIGGER VAL_TESTTYPE_CLONING 
AFTER DELETE OR INSERT OR UPDATE OF TESTGROUP ON CLONING_TABLE 
FOR EACH ROW 
WHEN (new.TESTGROUP = 'HSSAG') 
BEGIN
  INSERT INTO CLONING_TABLE (TESTCOL, TESTGROUP) VALUES(:new.TESTCOL, 'HSBEJ');
END;

这是我尝试插入时出现的错误

INSERT INTO "CLMSDBA"."CLONING_TABLE" (TESTCOL, TESTGROUP) VALUES ('3', 'HSSAG')
ORA-04091: table CLMSDBA.CLONING_TABLE is mutating, trigger/function may not see it
ORA-06512: at "CLMSDBA.VAL_TESTTYPE_CLONING", line 2
ORA-04088: error during execution of trigger 'CLMSDBA.VAL_TESTTYPE_CLONING'
ORA-06512: at line 1


One error saving changes to table "CLMSDBA"."CLONING_TABLE":
Row 3: ORA-04091: table CLMSDBA.CLONING_TABLE is mutating, trigger/function may not see it
ORA-06512: at "CLMSDBA.VAL_TESTTYPE_CLONING", line 2
ORA-04088: error during execution of trigger 'CLMSDBA.VAL_TESTTYPE_CLONING'
ORA-06512: at line 1

Answer 1

我发现了一个非常好的解决变异触发器问题的方法。希望这能有所帮助。

Mutating trigger workaround