create or replace trigger a_to_b after insert on tb for each row
begin
insert into b.tb(col1,col2,...,coln) values(:new.col1,:new.col2,...,:new.coln);
end a_to_b;当然要求A用户能操作B用户的表才行。
begin
insert into b.tb(col1,col2,...,coln) values(:new.col1,:new.col2,...,:new.coln);
end a_to_b;当然要求A用户能操作B用户的表才行。
conn userB/pwd
grant select,insert on tb to userA;
MGR-00072: Warning: TRIGGER TEST1_TO_TEST2 created with compilation errors.
我建立了两个用户:test1,test2对应表空间test1,test2,以下是表空间中表的结构:
create table TEST_1 (
NO number(2),
JH VARCHAR2(20));
下面是我写的触发器代码:
create or replace trigger test1_to_test2 after insert on test_1 for each row
begin
insert into test2.test_1(NO,JH) values(:new.NO,:new.JH);
end test1_to_test2;
但是出现了以上的错误,请您看看什么原因,谢谢。
ORA-04091: table TEST2.TEST_1 is mutating, trigger/function may not see it
ORA-06512: at "TEST2.TEST1_TO_TEST2", line 2
ORA-04088: error during execution of trigger 'TEST2.TEST1_TO_TEST2'
这是为什么?
在test1中建触发器,并在test1中插入数据。此时触发器将自动在test2的表中添加相应数据
1、在test1、test2用户下建表:
create table TEST_1 (
NO number(2),
JH VARCHAR2(20));
2、在test2中给test1授权:
connect test1/test1@slcygc
grant select,insert on test_1 to test2;
3、在test1中建立触发器
create or replace trigger test1_to_test2 after insert on test_1 for each row
begin
insert into test2.test_1(NO,JH) values(:new.NO,:new.JH);
end test1_to_test2;
4、在test1中插入数据
insert into test_1 (no,jh) values('1','1111')
前面的所有步骤都是成功的,只是在插入数据后,出现下面的错误:
ORA-04091: table TEST2.TEST_1 is mutating, trigger/function may not see it
ORA-06512: at "TEST2.TEST1_TO_TEST2", line 2
ORA-04088: error during execution of trigger 'TEST2.TEST1_TO_TEST2'
ORA-06512: at "TEST1.TEST1_TO_TEST2", line 2
ORA-04088: error during execution of trigger 'TEST1.TEST1_TO_TEST2'
诸位大侠看看到底是什么原因,谢谢,万分感激!!
grant select,insert on test_1 to test2;
------------------------------------------
改成
conn test2/password@slcygc
grant select,insert on test_1 to test1;
其它的不变,再试试
ORA-04091: table TEST2.TEST_1 is mutating, trigger/function may not see it
ORA-06512: at "TEST2.TEST1_TO_TEST2", line 2
ORA-04088: error during execution of trigger 'TEST2.TEST1_TO_TEST2'
ORA-06512: at "TEST1.TEST1_TO_TEST2", line 2
ORA-04088: error during execution of trigger 'TEST1.TEST1_TO_TEST2'是不是前面的操作不能在客户端上进行,而是必须在数据库服务器上进行?