解决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;
/