Re: Delete Trigger/Mutating Table Problem

The error seems to be in the WHERE NOT EXISTS - of the 'CollEmailADS' - Trigger.
You referenced email.email_no, you have to reference coll_email.email_no ! because you want to check the existence in the coll_email table ( SELECT coll_email.email_no FROM coll_email )

Tables to be deleted cannot be referenced as a target in WHERE NOT EXISTS or WHERE ... IN - Clauses : ( See 'Action :' )

ORA-04091 table name is mutating, trigger/function may not see it

Cause: A trigger or a user-defined PL/SQL function that is referenced in the statement attempted to query or modify a table that was in the middle of being modified by the statement that fired the trigger. Action: Rewrite the trigger or function so it does not read the table.

Copyright (C) 1995, Oracle Corporation
Gail Binkley <gail_at_fafner.Stanford.EDU> schrieb in im Newsbeitrag: 811fu6$mj9$1_at_nntp.Stanford.EDU...
> I have 3 tables: colleague, email, and coll_email (a linking table as
> a result of a many-many relationship). When a colleague is deleted,
> the record in the coll_email table should be automatically deleted.
> The record in the email table should be deleted only if no other
> colleague links to that email record.
>
> Delete cascade exists on the colleague_no foreign key in the
> coll_email table. There is no delete cascade on the email_no foreign
> key in the coll_email table.
>
>
> CREATE OR REPLACE PACKAGE EmailCleanUp AS
> TYPE emailnoArray IS TABLE OF coll_email.email_no%TYPE
> INDEX BY BINARY_INTEGER;
> emailNo emailnoArray;
> empty emailnoArray;
> END EmailCleanUp;
>
>
> CREATE OR REPLACE TRIGGER CollEmailBDS
> BEFORE DELETE ON coll_email
> BEGIN
> EmailCleanUp.emailNo := EmailCleanUp.empty;
> END CollEmailBD;
>
>
> CREATE OR REPLACE TRIGGER CollEmailADR
> AFTER DELETE ON coll_email
> FOR EACH ROW
> BEGIN
> EmailCleanUp.emailNo(EmailCleanUp.emailNo.count+1) := :old.email_no;
> END Coll_EmailAD;
>
>
> CREATE OR REPLACE TRIGGER CollEmailADS
> AFTER DELETE ON coll_email
> BEGIN
> FOR i IN 1 .. EmailCleanUp.emailNo.count LOOP
> DELETE FROM email
> WHERE NOT EXISTS (SELECT email_no from coll_email
> WHERE email.email_no = EmailCleanUp.emailNo(i));
> END LOOP;
> END Coll_EmailADS;
>
>
> I've been unsuccessful in avoiding the mutating table error with the
> above delete triggers. Can someone advise on the right approach in
> this situation? Thanks.
>
> - Gail
>
> Stanford University
> gail_at_genome.stanford.edu
> --
> Department of Genetics Phone: (650) 498-7145
> School of Medicine Fax: (650) 723-7016
> Stanford University Email: gail_at_genome.stanford.edu
> Stanford, CA 94305-5120 URL: http://genome-www.stanford.edu/
Received on Thu Nov 18 1999 - 14:51:23 CST