标签归档：ORA-04091

原因：在行级触发器中，不能查询自身表
场景重现：通过触发器实现test_count表中统计test表中行数

--创建子表
create table TEST
(id NUMBER, name varchar2(100), primary key (id));
--创建统计表
create table test_count (test_count int);
--创建触发器
CREATE OR REPLACE TRIGGER T_TEST
  AFTER INSERT OR DELETE ON TEST
  FOR EACH ROW
DECLARE
  A NUMBER;
BEGIN
  SELECT  COUNT(*) INTO A FROM TEST;
  UPDATE TEST_COUNT SET TEST_COUNT = A;
END T_TEST;

模拟错误：

INSERT INTO TEST (ID,NAME)VALUES(2,'abc');
ORA-04091: table CHF.TEST is mutating, trigger/function may not see it
ORA-06512: at "CHF.T_TEST", line 2
ORA-04088: error during execution of trigger 'CHF.T_TEST'

处理方法：
通过自治事务实现（修改触发器）

CREATE OR REPLACE TRIGGER T_TEST
  AFTER INSERT OR DELETE ON TEST
  FOR EACH ROW
DECLARE
  A NUMBER;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT  COUNT(*) INTO A FROM TEST;
  UPDATE TEST_COUNT SET TEST_COUNT = A;
  COMMIT;
END T_TEST;

PRAGMA AUTONOMOUS_TRANSACTION
当前的触发器作为已有事务的子事务运行，子事务自治管理，子事务的commit、rollback操作不影响父事务的状态