Re: Conditional Trigger

On Nov 21, 3:35 pm, 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 -

Ken, I tried your code but it did not track the periodical# ! = :new.periodical#_owner condition. It also allowed null values in the periodical#_owner field.

Regards,
Mark Received on Wed Nov 21 2007 - 15:38:38 CST