oracle触发器中屏蔽主事务,触发器引起ORA-04091

一、触发器引起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操作不影响父事务的状态

二、oracle触发器中增删改查本表

(1)只有before insert触发器中才可以查询或更新本表

create or replace trigger tri_test_ins

before insert

on test

for each row

declare

v_cnt integer;

begin

select count(*) into v_cnt from test;

dbms_output.put_line('test count:'||to_char(v_cnt));

update test set a9='99';

end;

执行insert后，只有当前插入的记录值不是99，其它的记录都被更新成了99。

(2)before/after update、before/after delete、after insert5种情况都不可以查询或更新本表。

(3)使用自治事务可以实现任意触发器查本表。但不能实现在自治事务中更新本表。

查询本表的情况是最常见的。

create or replace trigger tri_test_ins

after update

on test

for each row

declare

v_cnt integer;

PRAGMA AUTONOMOUS_TRANSACTION;

begin

begin

select count(*) into v_cnt from test;

dbms_output.put_line('test count:'||to_char(v_cnt));

end;

end;

(4)使用自治事务可以实现新增或删除本表的记录。这种情况一般不会用到。

三、自治事务查本表使用:new错误

create or replace

trigger tr_mtn_basic_data_change

after insert

on mtn_basic_data_change

for each row

declare

vc_change_operator varchar2(10);

PRAGMA AUTONOMOUS_TRANSACTION;

begin

select change_operator into vc_change_operator from mtn_basic_data_change where scn=:new.scn;

update mtn_basic_data_change_log set change_operator=vc_change_operator;

commit;

end;

由于insert mtn_basic_data_change还没有提交，select change_operator into vc_change_operator from mtn_basic_data_change where scn=:new.scn语句报1403错。

解决方法：修改触发器如下：

create or replace

trigger tr_mtn_basic_data_change

after insert

on mtn_basic_data_change

for each row

declare

vc_change_operator varchar2(10);

begin

vc_change_operator:=:new.change_operator;

update mtn_basic_data_change_log set change_operator=vc_change_operator;

end;

阅读(1209) | 评论(0) | 转发(0) |