Re: Problem with delete trigger

Hi,

  Create a temp table. Insert the key values of the rows that are   being deleted into this table using the row level delete trigger.

  Use a statement level delete trigger at the table to look into the   temp table and update your prime table accordingly.

  This would work...

Regards
Rajagopal Venkataramany

On Mon, 19 Jun 2000 10:58:01 -0800, ORACLE-L_at_fatcity.com wrote:

> 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;
> /
>
>
> ________________________________________________________________________
> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
> --
> Author: Guang Mei
> INET: zlmei_at_hotmail.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

Regards
Rajagopal Venkataramany Received on Tue Jun 20 2000 - 10:14:28 CDT