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-Hide quoted 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 ...
Received on Wed Nov 21 2007 - 08:35:01 CST
