Problem with delete trigger

From: <gmei_at_my-deja.com>
Date: 2000/06/19
Message-ID: <8ilmcu$r9q$1@nnrp1.deja.com>#1/1

Hi:

I am getting this table mutating error from my trigger code. I know why it happens but can not seem to find a work-around if I still want to use trigger.

Basically I have a table HPXPRODUCTCATEGORY,

SQLWKS> desc HPXPRODUCTCATEGORY

Column Name                    Null?    Type
------------------------------ -------- ----
PRODUCTID                      NOT NULL NUMBER(9)
CATEGORYID                     NOT NULL NUMBER(9)
PRINCIPAL                      NOT NULL CHAR(1)

PRINCIPAL is a flag with values 'T' or 'F'. For each PRODUCTID, I want to have one and only one PRINCIPAL ='T'. The insert and update part in trigger code works fine. The problem comes from delete part.

Here is what I want trigger code to do:

When deleting a record,
 if the record is non-principal record, do nothing  end if;

 if the record is a principal record then

     if there are other record(s) with the same PRODUCTID,
        raise exception
     else
        no nothing
     end if;

 end if

The problem in my trigger code is I have "select ..." which results table mutation error.

I probably can remove the "deleting" part out of trigger code and use the front end java code to "check" before deleting record(s). But I thought it would be nice if I could have all the code in one place (trigger).

Any suggestions?

Thanks.

Guang

PS: Here is the trigger code:

---

CREATE OR REPLACE TRIGGER hpxtrg_category_chk_principal before insert or update or delete on HPXPRODUCTCATEGORY for each row

---

DECLARE

  lCOUNT                  INTEGER;
  lCOUNT_PRINCIPAL        INTEGER;

  lCOUNT_NON_PRINCIPAL INTEGER;

BEGIN   IF INSERTING or UPDATING THEN

    SELECT COUNT(*)
    INTO lCOUNT
    FROM HPXPRODUCTCATEGORY
    WHERE PRODUCTID = :new.PRODUCTID
    AND PRINCIPAL = 'T';     IF lCOUNT = 0 THEN

      IF :new.PRINCIPAL != 'T' THEN
         RAISE_APPLICATION_ERROR (-20001, 'Error: This product has no
Principal Category');
      END IF;

    ELSIF lCOUNT = 1 THEN

      IF :new.PRINCIPAL = 'T' THEN
         RAISE_APPLICATION_ERROR (-20002, 'Error: This product already
has a Principal Category');
      END IF;

    ELSIF lCOUNT > 1 THEN
      RAISE_APPLICATION_ERROR (-20003, 'Error: This product has

multiple Principal Categories');

    ELSE
      NULL;
    END IF;   END IF;   IF DELETING THEN     IF :old.PRINCIPAL != 'T' THEN

• deleting non-principal record, it's OK NULL;

    ELSIF :old.PRINCIPAL = 'T' THEN -- try to delete principal

      SELECT COUNT(*)
      INTO   lCOUNT_NON_PRINCIPAL
      FROM   HPXPRODUCTCATEGORY
      WHERE  PRODUCTID = :old.PRODUCTID
      AND    PRINCIPAL != 'T';

      IF lCOUNT_NON_PRINCIPAL = 0 THEN
         --- no non-principal exists, it's OK to delete principal
         NULL;
      ELSIF lCOUNT_NON_PRINCIPAL > 0 THEN
         RAISE_APPLICATION_ERROR (-20004, 'Error: Non Principal
Category still exist');
      END IF;

    ELSE
      NULL;

    END IF;   END IF; END;
/

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jun 19 2000 - 00:00:00 CDT