在trigger中如何操作变异表

CREATE OR REPLACE TRIGGER TR_E_FILE2 BEFORE INSERT OR UPDATE OR DELETE ON E_FILE2 FOR EACH ROW
DECLARE 
  seqno INTEGER;
  TYPE cursor_type IS REF CURSOR;
  cursor1 cursor_type;
  cursor2 cursor_type;
  efile_record e_file2%ROWTYPE;
BEGIN
  IF INSERTING THEN
    IF :NEW.ext='PDF' THEN
      SELECT LIBFILE2_SEQ.NEXTVAL INTO seqno FROM DUAL;
      INSERT INTO T_FILEINF2(SEQNO,"MODE",F_ID)VALUES(seqno,1, :NEW.DID);
      OPEN cursor1 FOR SELECT * FROM e_file2 WHERE ext='PDF' START WITH did=:NEW.did CONNECT BY PRIOR edid=did ORDER BY iscurr DESC;
      FETCH cursor1 INTO efile_record;
      FETCH cursor1 INTO efile_record;
      WHILE cursor1%FOUND LOOP
        DELETE FROM T_FILEINF2 WHERE f_id=efile_record.did;
        FETCH cursor1 INTO efile_record;
      END LOOP;
      CLOSE cursor1;
    ELSIF :NEW.ext='tif' THEN
      SELECT LIBFILE2_SEQ.NEXTVAL INTO seqno FROM DUAL;
      INSERT INTO T_FILEINF2(SEQNO,"MODE",F_ID)VALUES(seqno,1, :NEW.DID);
    END IF;
  ELSIF UPDATING THEN
    IF :NEW.ext='PDF' THEN
      OPEN cursor2 FOR SELECT * FROM e_file2 WHERE ext='PDF' START WITH did=:NEW.did CONNECT BY PRIOR did=edid ORDER BY iscurr DESC;
      FETCH cursor2 INTO efile_record;
      IF efile_record.iscurr=:NEW.iscurr THEN
        SELECT LIBFILE2_SEQ.NEXTVAL INTO seqno FROM DUAL;
        INSERT INTO T_FILEINF2(SEQNO,"MODE",F_ID) VALUES(seqno,2,:NEW.DID);
      END IF;
      CLOSE cursor2;
    ELSIF :NEW.ext='tif' THEN
      SELECT LIBFILE2_SEQ.NEXTVAL INTO seqno FROM DUAL;
    INSERT INTO T_FILEINF2(SEQNO,"MODE",F_ID) VALUES(seqno,2,:NEW.DID);
    END IF;
  ELSIF DELETING THEN
    SELECT LIBFILE2_SEQ.NEXTVAL INTO seqno FROM DUAL;
    INSERT INTO T_FILEINF2(SEQNO,"MODE",F_ID) VALUES (seqno,3,:OLD.DID);
   
  END IF;
END;

 

在执行下列语句时：

INSERT INTO e_file2(did,status,attr,attrex,ext) VALUES(333,0,0,0,'PDF');

UPDATE e_file2 SET title='aaabbbcccc' WHERE did=29;

都会出现异常：

ORA-04091: 表 JXSDAG.E_FILE2 发生了变化，触发器/函数不能读
ORA-06512: 在"JXSDAG.TR_E_FILE2", line 26
ORA-04088: 触发器 'JXSDAG.TR_E_FILE2' 执行过程中出错

 

因为在该触发器中使用到了触发表，而触发表现在处于变异状态，所以从中读取数据时将提示上述信息。

解决方案：在declare后加入自治事务语句PRAGMA   AUTONOMOUS_TRANSACTION;

再执行上述两个语句，又产生了异常

 

ORA-06519: 检测到活动的自治事务处理，已经回退
ORA-06512: 在"JXSDAG.TR_E_FILE2", line 40
ORA-04088: 触发器 'JXSDAG.TR_E_FILE2' 执行过程中出错

 

是因为没有对自治事务进行事务处理，在end之前加入commit，问题解决！