关于trigger的muting table异常

研究方法：为测试表建立 insert,delete,update 对应的after，before触发器，在触发器中访问当前表，然后执行测试的 insert,delete,update 语句触发这个触发器， 查看是否有异常：
ORA-04091: table YC.TR1 is mutating, trigger/function may not see it


1. 建表，并插入测试数据
create table tr1
(id number,name varchar2(10));
insert into tr1 values(1,'a');

 

2.建立触发器并执行测试

create or replace trigger tr1_bu
before update on tr1 for each row
declare
a number;
begin
select id into a from tr1 where id = 1;
end;

-- 执行
update tr1 set name='b'  -- 报错

drop trigger tr1_bu;

create or replace trigger tr1_au
after update on tr1 for each row
declare
se number;
begin
select id into se from tr1 where id =1;
end;

--执行
update tr1 set name = 'b' where id = 1  --报错



create or replace trigger tr1_ai
after insert on tr1 for each row
declare
a number;
begin
select id into a from tr1 where id = 1;
end;

--执行
insert into tr1 values(2,'b'); --报错


create or replace trigger tr1_bi
before insert on tr1 for each row
declare
a number;
begin
select id into a from tr1 where id = 1;
end;

--执行
insert into tr1 values(2,'b'); --成功

insert into tr1
select 1,'a' from dual -- 报错



create or replace trigger tr1_ad
after delete on tr1 for each row
declare
a number;
begin
select id into a from tr1 where id = 1;
end;

--执行
delete from tr1 where id = 2 --报错


drop trigger tr1_ad;

create or replace trigger tr1_bd
before delete on tr1 for each row
declare
a number;
begin
select id into a from tr1 where id  =1;
end;

--执行
delete from tr1 where id = 2 --报错

 

 

总结： 只有在before insert触发器中才能编写访问当前表数据的sql语句，但是前提是用普通insert语句触发这个触发器，否则如果用insert select 来触发这个触发器，就会出错