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

The documentation on triggers talks about mutating and constraining tables.

The only way to access a table which is constraining or mutating is to use an after table trigger not an after row trigger

ie. CREATE TRIGGER ... AFTER DELETE ON TBL BEGIN ... NOT - CREATE TRIGGER ... AFTER DELETE ON TBL FOR EACH ROW BEGIN ... The problem with this is that you will need to store some information in a temporary table so that the trigger knows what to work with. You will need the row trigger to do this.

In this case it is easier to just use the ON DELETE CASCADE clause.

Peter Laursen wrote:

> 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 - 18:15:52 CST