Re: trigger mutation help

From: Yogesh Sharma <sharmayNOSPAM_at_usa.net.NOSPAM>
Date: 2000/05/12
Message-ID: <391C6746.3D65D8DA_at_usa.net.NOSPAM>#1/1


I replied it on comp.database.oracle.server news group

Gary Knopp wrote:

> I am trying to construct trigger(s) that stores all the
> data of a Parent/Child table relationship in a single
> History table when a record change is made to either
> the Parent or Child record.
>
> I am able to accomplish ALL modification events EXCEPT
> when a Parent record is deleted. In this instance I want
> all the Parent's Children records deleted as well
> after storing their old data relationship in the history
> table. However,I get a table mutation error.
>
> Here is an basic example of of what I'm trying to do...
>
> Parent Child History
> =============== ============ ================
> Pkey subject Fkey item subject item
> --- ------- ----- ------ trig --------- --------
> 1 colors 1 red ==> colors red
> 2 cars 2 camry cars camry
> 3 sports 2 volvo cars volvo
> 3 baseball sports baseball
> 3 soccer sports soccer
>
> Child Trigger:
> after update or insert or delete for each row
>
> + (update): query parent field into variable and insert
> OLD record into history table
>
> + (insert): query parent field into variable and insert
> NEW record into history table
>
> + (delete): query parent field into variable and insert
> OLD record into history table
>
> Parent Trigger:
> after update for each row
>
> + (update): create CURSOR to query children and insert
> old record(s) into history table
>
> Note:
> On 'insert' I don't want anything to be stored in
> the history table. I'll wait until a child record
> is created
>
> For Parent delete:
> I tried using a FK constraint on Child (on delete cascade)
> but I get a mutation problem, since I'm tring to delete
> Parent while trying to query its value though Child for
> the insert into the history table.
>
> How can I accomplish what I'm trying to do?
Received on Fri May 12 2000 - 00:00:00 CEST

Original text of this message