Re: Problem with delete trigger

You need to create a statement trigger as opposed to a row trigger. SQL in trigger could be something where you'd define a cursor to do the following and then within your loop, if you get at least one record back, spit out the appropriate error.

SELECT productid, COUNT(*)
  INTO tmp_productid, icount
  FROM table
 WHERE PRINCIPAL = 'T'
GROUP BY productid
HAVING COUNT(*) <> 1

Jake de Haan.

> INTO lCOUNT
> FROM HPXPRODUCTCATEGORY
> WHERE PRODUCTID = :new.PRODUCTID
> AND PRINCIPAL = 'T';
<gmei_at_my-deja.com> wrote in message news:8ilmcu$r9q$1_at_nnrp1.deja.com...
> 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