Re: Conditional Trigger

mowinom_at_hotmail.com wrote:
> Hello all,
>
> I have the following task: I have a table with two fields, ISBN
> (NUMBER 9), TITLE and PERIODICAL_OWNER (NUMBER 9). I am trying to
> create a trigger that checks on insert, that the ISBN being entered is
> not already in the table but if it does, it must exist in the
> PERIODICAL_OWNER column.
>
> So, if the new ISBN being entered is already in the table, it has to
> be in the PERIODICAL_OWNER column in order for it to be accepted. I am
> new to PL/SQL and have tried writing a trigger but it's not working.
> Here it is:
>
> IF :New.ISSN = :Old.ISSN And
> :New.ISSN NOT IN (:Old.Tidsskriftnr_eier) THEN
> RAISE_APPLICATION_ERROR(-20070, 'ERROR>>' ||
> ' PERIODICAL:' ||
> ' For ISSN that already exists in the basen, it' ||
> ' must exist in periodicald to be accepted.');
> ELSE
> :New.ISSN := :New.ISSN;
> END IF;
>
> Thank you in advance for your help
>
> --
> Me

That you are new is obvious given that you think posting actual error message, "but it's not working" is a worthless statement, unnecessary and that you made a mistake that has been documented since Oracle 6.0.

The "Mutating" error is caused by trying to do something that is not allowed. Go to http://tahiti.oracle.com and look up "Trigger" and "Mutating."

The solution is to fix your design. Given that you didn't post it I can't tell you what that fix should be. But I can tell you that to be successful in Oracle requires reading the docs.

Generally speaking what you are trying to do is unnecessary with a good design. If it is impossible to enter something in table A without a corresponding entry in B then existence in A guarantees existence in B. Finding out whether something already exists does not require a SELECT COUNT(*) ... it only requires that the insert fails due to a violation of a primary key or unique constraint. No trigger is required.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org