Re: How to update child tables using trigger?

Kirmo Uusitalo wrote:
>
> create table k_node(
> ID NUMBER(38) NOT NULL primary key
> );
>
> create table k_line(
> ID NUMBER(38) NOT NULL primary key,
> INPUT_NODE NUMBER(38),
> constraint fk_input_node foreign key (input_node) references
> k_node(id)
> );
>
> create or replace trigger k_node_rd
> before delete on k_node
> for each row
> begin
> update k_line set input_node=NULL where input_node=:old.id;
> end;
> /
>
> This is what happens:
>
> SQL> delete from k_node where id=1;
> delete from k_node where id=1
> *
> ERROR at line 1:
> ORA-04091: table SCOTT.K_NODE is mutating, trigger/function may not
> see it
> ORA-06512: at line 2
> ORA-04088: error during execution of trigger 'SCOTT.K_NODE_RD'
>
> If I drop the foreign key constraint fk_input_node from k_line table,
> then the trigger executes OK. If someone could suggest a method to
> achieve this kind of functionality without losing the referential data
> integrity, I'd be more than happy.

According to the constraints you set up for the table k_line, an update of the column input_node cannot take place unless the new value matches an entry in l_node. However, as you are informed in the error msg, k_node is mutating (you are, after all, right in the middle of a delete statement). So the referential check cannot take place.

Oracle allows two restrictions in foreign key constraints: 1) restrict - don't allow the modification of the referenced column or the deletion of the row if it results in a foreign key in a child table without a proper reference value.
2) cascade - allow the update or delete of the master table but go and delete all rows in the child table(s) that refers to the updated/deleted row.

As you want to implement a third alternative, you must take over the constraint checking yourself. You have to remove the foreign key constraint.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"