Re: How to update child record in trigger of master_table?

Either change before in after
or if you have Oracle 8 or beyond you can define the constraint to nullify your key. In that case simply drop the trigger.

Hth,

Sybrand Bakker, Oracle DBA

<corvax_kg_at_my-deja.com> wrote in message news:8u0ef6$s4v$1_at_nnrp1.deja.com...
> When I am deleting a record from master table I have to update record
> (s) in child table. I try to update child record in trigger BEFORE
> DELETE ON master_table FOR EACH ROW. In the body of that trigger I have
> to set FOREIGN KEY field value in child_table to NULL. But when trigger
> fired oracle error is appeared "Table is mutating...".
> The same situation has been discussed in this forum, but discussed
> situation was about INSERTing into master_table...
> Help, please.
> Thanks in Advance.
>
> Here are details:
> create or replace trigger T_master_set_null
> before delete on t_master
> for each row
> declare
> begin
> update t_child
> set id_master = null
> where id_master = :old.id;
> end T_master_null;
> --
> And full text of error message is:
> ORA-04091: table T_MASTER is mutating, trigger.function may not see it.
> ORA-06512: at "T_MASTER_SET_NULL", line 6
> ORA-04088: error during execution of trigger 'T_MASER_SET_NULL'
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Sat Nov 04 2000 - 03:16:04 CST