Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Conditional Trigger

Re: Conditional Trigger

From: Ken Denny <ken_at_kendenny.com>
Date: Wed, 21 Nov 2007 10:44:56 -0800 (PST)
Message-ID: <07e42943-5e25-4921-b487-303e7108c3d2@w28g2000hsf.googlegroups.com>


On Nov 21, 10:03 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Nov 21, 8:35 am, Ken Denny <k..._at_kendenny.com> wrote:
>
>
>
>
>
> > On Nov 21, 1:56 am, mowi..._at_hotmail.com wrote:
>
> > > On Nov 20, 9:59 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > > mowi..._at_hotmail.com wrote:
> > > > > On 20 Nov, 11:25, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> > > > >> mowi..._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
> > > > >> Ever heard of modeling and normalization?
>
> > > > >> You have two tables: ISBN is a primary key (which will
> > > > >> guarantee uniqueness of ISBN numbers) on the first, and
> > > > >> your second table has Periodical as PK, and a foreign key
> > > > >> relation to ISBN (mandatory).
>
> > > > >> Really! second class, before Xmas!
> > > > >> --
> > > > >> Regards,
> > > > >> Frank van Bortel
>
> > > > >> Top-posting is one way to shut me up...- Skjul sitert tekst -
>
> > > > >> - Vis sitert tekst -
>
> > > > > Thanks Frank, but we are dealing with one table here.
>
> > > > > Regards,
> > > > > Mark (Me)
>
> > > > Only if you refuse to use a relational database as a relational
> > > > database. Frank is suggesting that you use the tool properly.
> > > > --
> > > > Daniel A. Morgan
> > > > Oracle Ace Director & Instructor
> > > > University of Washington
> > > > damor..._at_x.washington.edu (replace x with u to respond)
> > > > Puget Sound Oracle Users Groupwww.psoug.org-Hidequotedtext -
>
> > > > - Show quoted text -
>
> > > This, still, is using a relational database as a relational database.
> > > The only new thing here is that the relation has been specialized at
> > > the column level; an aspect not currently explicitly supported by
> > > Oracle. And therefore, the need to use a trigger. Let me look through
> > > the link you provided previously and see if it sheds some light(we're
> > > in different time zones 6+ hours, I'm in Norway).
>
> > > Frank's suggestion was a natural and a good one. And yes, I know about
> > > modeling and normalization. Problem is that a pannel decided that it
> > > be done as specified above. There are already too many tables in the
> > > database already.
>
> > SELECT COUNT(*) INTO cnt
> > FROM periodical
> > WHERE isbn = :new.isbn
> > AND periodical# != :new.periodical#;
> > IF cnt != 0
> > THEN
> > RAISE_APPLICATION_ERROR ...- Hide quoted text -
>
> > - Show quoted text -
>
> It would help you tremendously to READ the specification provided in
> the original post as that behaviour is NOT the desired outcome.
> Records are rejected when the ISBN already exists and the PERIODICAL#
> field is not properly populated with reference to that ISBN.
>
> Your 'solution' is half-baked, at best.
>
> David Fitzjarrell

I did read the original post and it was poorly worded so I had difficulty discering the poster's intent. So maybe I got wrong. I surmised from the bad code in some of the later posts, that the intent was that if the ISBN already existed then the PERIODICAL# must match that of the already existing ISBN. If that was not the case then I apologize for misunderstanding.

As for:
> Records are rejected when the ISBN already exists and the PERIODICAL#
> field is not properly populated with reference to that ISBN.

what does "not properly populated with reference to that ISBN" mean? Received on Wed Nov 21 2007 - 12:44:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US