Re: Conditional Trigger

From: <mowinom_at_hotmail.com>
Date: Tue, 20 Nov 2007 22:56:39 -0800 (PST)
Message-ID: <eaa7a5eb-332f-40f7-b25e-b14a7ec99d5c@e4g2000hsg.googlegroups.com>

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.
Mark (Me) Received on Wed Nov 21 2007 - 00:56:39 CST