RE: Problem with delete trigger

Hi,

To solve your problem, you can move your select ... to a function, which must be placed in a package, and in the package specification you should add 'pragma restrict_references( <function_name>, WNDS);', with which you promise not to change the database state.

Eg. your header would look something like this:

CREATE OR REPLACE PACKAGE package_name as

function does_another_record_exist (productid number) return boolean;

pragma restrict_references( does_another_record_exist, WNDS );

end package_name;
/

You can now use the function in your trigger.

This is all off the top of my head, so please beware for syntax errors.

HTH Remco

> ----------
> From: Guang Mei[SMTP:zlmei_at_hotmail.com]
> Sent: maandag 19 juni 2000 20:58
> To: Multiple recipients of list ORACLE-L
> Subject: Problem with delete trigger
>
> 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
Received on Tue Jun 20 2000 - 05:08:56 CDT