Re: Help! - ORA-04094: table XX is constraining, trigger may not modify it

I guess you first have to insert 'FRED' into emp_master.

Martin

Graham Thornton wrote:
>
> 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
> --------------------
> SCOTT
>
> SQL> select * from emp_detail;
>
> 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<----------
> Demo Script follows:
>
> /* 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;
> /
Received on Sat Apr 01 2000 - 00:00:00 CST