Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Help! - ORA-04094: table XX is constraining, trigger may not modify it

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

From: Graham Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: 2000/03/30
Message-ID: <38E36C99.E4A1F2A0@ln.ssw.abbott.com>#1/1

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 Thu Mar 30 2000 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US