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

Home -> Community -> Usenet -> c.d.o.misc -> ORA-04094: table name is constraining, trigger may not modify it

ORA-04094: table name is constraining, trigger may not modify it

From: Peter Laursen <ptl_at_edbgruppen.dk>
Date: 11 Feb 1999 13:52:22 GMT
Message-ID: <01be55ce$46f26ff0$2c289a0a@apollo>


Help
I have two simple tables(t1 and t2) and a third(t3) that consist of to columns, with the primary key froms each of t1 and t2. When deleting a row from t1 it fails if there is a corresponding row in t3. To solve this I make a trigger 'before delete on t1' to delete the offending rows in t3.
However Oracle will not let me do this. The trigger compiles but executes with an ora-04094 error.
It says: A trigger attempted to modify a table that was constraining for some referential constraint of a parent SQL statement Im not quite sure what that means:)
(im pretty sure I used to do these kind of triggers in InterBase)

Please help
Peter

create table t1 (a number constraint t1_pk primary key);

create table t2 (b number constraint t2_pk primary key);

create table t3 (
a number constraint t3_a references t1(a), b number constraint t3_b references t2(b));

create or replace trigger delete_t1
before delete on t1
FOR each row
BEGIN
delete from t3 where t3.a = :old.a;
END;
/

insert into t1 (a) values(1);
insert into t2 (b) values(1);
insert into t3 (a,b) values(1,1);

delete from t1 where a = 1;
/* trigger fails on execute with ora4094 Received on Thu Feb 11 1999 - 07:52:22 CST

Original text of this message

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