研究方法:为测试表建立 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 来触发这个触发器,就会出错