Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Help! - ORA-04094: table XX is constraining, trigger may not modify it
Can anybody help me figure out this one?
We have a situation where we want to keep audit information on a number of tables, and need to establish parent/child relationships that are not dependent on the version of the record.
We have developed a spilt-table design with a master and detail for each entity requiring auditing, and a foreign key constraint between the master and detail tables.
To make life easier for the development guys, a trigger on the detail
table will auto-insert
a record into the master table if one is required. So far this works
very well, but one of our analysts ran into a problem when he tried to
copy some data using an insert-into-select-from type construct:
SQL> insert into emp_detail values ( 'SCOTT', '10 Main St', 1000, 1 ); 1 row created.
SQL> insert into emp_detail values ( 'SCOTT', '10 Main St', 2000, 2 ); 1 row created.
SQL> select * from emp_master;
NAME
NAME
ADDRESS SALARY VERSION -------------------- -------------------------------------------------- --------- --------- SCOTT 10 Main St 1000 1 SCOTT 10 Main St 2000 2
So far so good, but when we try:
SQL> insert into emp_detail (
2 select 'FRED', address, salary, version
3 from emp_detail where name = 'SCOTT' and version = 1 );
insert into emp_detail (
*
ERROR at line 1:
ORA-04094: table ORENTMAN.EMP_MASTER is constraining, trigger may not
modify it
ORA-06512: at "ORENTMAN.EMP_DETAIL_BITRG", line 5
ORA-04088: error during execution of trigger 'ORENTMAN.EMP_DETAIL_BITRG'
If I remove the foreign key constraint, or drop the trigger, the problem
goes away. But
how come it only happens with the insert-select statement and not with
the insert-values
statement?
I don't want to drop either the constraint or the trigger - anybody got any suggestions?
Thanks
Graham
------------------8<----------
/* employee table with full versioning */
CREATE TABLE EMP_DETAIL
(NAME VARCHAR2(20) NOT NULL
,ADDRESS VARCHAR2(50) NOT NULL
,SALARY NUMBER(10,2) NOT NULL
,VERSION NUMBER(8) NOT NULL
)
/
/* employee table with no versioning/detail */
CREATE TABLE EMP_MASTER
(NAME VARCHAR2(20) NOT NULL)
/
alter table EMP_DETAIL add constraint
EMP_DETAIL_PK primary key ( NAME,VERSION )
/
alter table EMP_MASTER add constraint
EMP_MASTER_PK primary key ( NAME )
/
alter table EMP_DETAIL add constraint EMP_DETAIL_FKM
foreign key (NAME)
references EMP_MASTER (NAME)
/
/* trigger to auto-insert name into master table */
create or replace trigger EMP_DETAIL_BITRG
before insert on EMP_DETAIL
for each row
begin
/* add the record to the master table */ if( :new.VERSION = 1 ) then insert into EMP_MASTER ( select :new.NAME from dual where not exists ( select null from EMP_MASTER where NAME = :new.NAME )); end if;
end;
/
create or replace trigger EMP_DETAIL_ADTRG
after delete on EMP_DETAIL
begin
/* clean up master table after delete statement */ delete from EMP_MASTER EM where 1 > ( select count( ROWID ) from EMP_DETAIL ED where ED.NAME = EM.NAME );end;