trigger mutation help

From: Gary Knopp <gknopp_at_ascend.com>
Date: 2000/05/12
Message-ID: <8fhk7q$f0b_at_nntpa.cb.lucent.com>#1/1


[Quoted] 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