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

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Thu, 11 Feb 1999 11:11:56 -0800
Message-ID: <79va0b$nlj$1@oak.prod.itd.earthlink.net>

Here is the solution:

---

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,
b number,
constraint t3_a foreign key (a)

   references t1(a) on delete cascade, constraint t3_b foreign key (b)

   references t2(b) on delete cascade
);

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

Peter Laursen wrote in message <01be55ce$46f26ff0$2c289a0a_at_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 - 13:11:56 CST