Re: Conditional Trigger

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-Hidequoted text -
>
> > > - 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 Received on Wed Nov 21 2007 - 09:03:47 CST