Delete Trigger/Mutating Table Problem

From: Gail Binkley <gail_at_fafner.Stanford.EDU>
Date: 18 Nov 1999 18:18:46 GMT
Message-ID: <811fu6$mj9$1@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 - 12:18:46 CST