trigger mutation help
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