解决ORA-04091行触发器中访问变异表的偏题

解决ORA-04091行触发器中访问变异表的难题

核心思路就是：在第一次插入时保存值到包变量中，第二次插入时不再读取表本身，转而读取包变量，可以成功解决这类难题。

在工作中，需要为各个BOM的每个ITEM依次自动编号，不同BOM的ITEM的SEQ_NUMBER列都要 按1.2.3…自动生成序号。
第一次，我这样写，

CREATE?OR?REPLACE?TRIGGER?TR_BOM_AUTONUMBER_SEQNUMBER
BEFORE?INSERT
ON?BOM?
REFERENCING?NEW?AS?NEW?OLD?AS?OLD
FOR?EACH?ROW
DECLARE
????tmpVar?number;
BEGIN
????tmpVar?:=?0;
???SELECT?GREATEST(nvl(Max(to_number(SEQ_NUMBER)),0),?Count(*))?+?1?INTO?tmpVar?FROM?BOM?Where?BOMID=:New.BOMID;
???:NEW.?SEQ_NUMBER:=?nvl(tmpVar,1);
END?TR_BOM_AUTONUMBER_SEQNUMBER;

由于Insert操作会修改表数据，所以Insert…Select插入多行数据时，会报ORA-04091: table string.string is mutating, trigger/function may not see it错误，原因在于插入第2条数据时表已修改不能再访问。

查阅了很多文章，有提示在其中使用 PRAGMA AUTONOMOUS_TRANSACTION来保证每行插入动作为自治事务。但实际上，经过我的测试，虽然DML不会出错，但实际SEQ_NUMBER全部为1，没有达到依次自动编号的目的。

经过多次试验后，我使用保存于包中的索引表保存各个BOM的最大SEQ_NUMBER，可以防止BOM之间及用户之间的并发冲突。

核心思路就是：在第一次插入时保存值到包变量中，第二次插入时不再读取表本身，转而读取包变量，可以成功解决这类难题。

详细代码如下：

CREATE?OR?REPLACE?PACKAGE?BOM_AUTONUMBER
IS
TYPE?t_MAX_SEQNUMBER?is?table?of?number?INDEX?BY?PLS_INTEGER;
v_MAX_SEQNUMBER?t_MAX_SEQNUMBER;
end?BOM_AUTONUMBER;
/CREATE?OR?REPLACE?TRIGGER?TR_BOM_AUTONUMBER_SEQNUMBER
BEFORE?INSERT
ON?BOM?
REFERENCING?NEW?AS?NEW?OLD?AS?OLD
FOR?EACH?ROW
DECLARE
????vNumber?number;
????vBOMID?number;
BEGIN
????vNumber:=?0;
????vBOMID:=?:New.BOMID;
????if?not?BOM_AUTONUMBER.v_MAX_SEQNUMBER.EXISTS(vBOMID)?then
????????SELECT?GREATEST(nvl(Max(to_number(SEQ_NUMBER)),0),?Count(*))?INTO?vNumber?FROM?BOM?Where?ITEM?=?vBOMID;
????????BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID)?:=?nvl(vNumber,?0);
????end?if;
????BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID)?:=?BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID)?+?1;
???:NEW.SEQ_NUMBER?:=?BOM_AUTONUMBER.v_MAX_SEQNUMBER(vBOMID);
END?TR_BOM_?AUTONUMBER_SEQNUMBER;
/

查看更多 下一篇