Re: mutation problem in triggers

On Thu, 05 Mar 1998 20:02:00 -0600, smrfld42_at_ix.netcom.com wrote:

It is likely that referential integrity constraints defined on the child tables are causing the problem. The insertions are instigated in the After Insert Row level trigger on the parent table CONTACT.Personal_Info, which is still considered mutating at that time. So, a Foreign Key constraint of the child table, directed toward the Personal_Info table, will encounter this problem.

One workaround is to postpone the inserts into the child tables until a statement level After Insert trigger. You would have to use the row level trigger to capture the "personal_key" value(s) being inserted by the statement (typically in a PL/SQL memory table defined within a package). The statement level After Insert trigger would then perform the inserts on the child tables for each "personal_key" value that was recorded by the row level trigger.

>I was wondering if anyone could tell me why these two triggers are
>conflicting. I get the following error:
>
>
>ERROR at line 1:
>ORA-04091: table CONTACT.PERSONAL_INFO is mutating, trigger/function may not
>see
> it
>ORA-06512: at "CONTACT.PERSONAL_POSTINSERT", line 4
>ORA-04088: error during execution of trigger 'CONTACT.PERSONAL_POSTINSERT'
>
>
>CREATE OR REPLACE TRIGGER personal_preinsert
>BEFORE INSERT ON personal_info
>FOR EACH ROW
>BEGIN
> /*Set the value of the key for the parent table*/
> SELECT contact_personal_seq.nextVal INTO :new.personal_key
> FROM dual;
>END;
>
>
>CREATE OR REPLACE TRIGGER personal_postinsert
>AFTER INSERT ON personal_info
>FOR EACH ROW
>BEGIN
> /*Create records in child tables with NULL Values*/
> /*Contact Table*/
> INSERT INTO contact_info VALUES
>(contact_contact_seq.NEXTVAL ,NULL,NULL,NULL, NULL,NULL, NULL,NULL,NULL,NULL,
>NULL, NULL,contact_personal_seq.currval);
>
> /*Additional Table*/
> INSERT INTO additional_info VALUES
>(contact_additional_seq.NEXTVAL,NULL, contact_personal_seq.CURRVAL);
>END;
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading

Bob Cunningham
bcunn_at_oanet.com Received on Fri Mar 06 1998 - 00:00:00 CST