Re: Conditional Trigger

On Nov 20, 10:27 am, mowi..._at_hotmail.com wrote:
> On Nov 20, 4:42 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Nov 20, 9:27 am, mowi..._at_hotmail.com wrote:
>
> > > Daniel and David, thanks.
>
> > > In deed, Daniel, they are four and not three as stated above, and I am
> > > sorry that I left some more details out when I wrote the first time.
> > > It's like this:
>
> > > For a new article to be added to the table with a ISBN that already
> > > exists in the in that field, it must exist in the PERIODICAL_OWNER
> > > field. The logic behind this is that instead of enforcing a UNIQUE
> > > constraint on ISBN a trigger is used to prevent unnecessary duplicates
> > > in ISBN. The reason for avoinding the unique constraint is that one or
> > > more articles may come in with the same ISBN in different periodicals.
> > > In this case, therefore, the PERIODICAL#_OWNER must be included and it
> > > refers to the the original PERIODICAL#.
>
> > > PERIODICAL# (NUMBER 9).
> > > ISBN (NUMBER 9),
> > > TITLE (VARCHAR2 500)
> > > PERIODICAL_OWNER (NUMBER 9)
>
> > > I did modify the trigger thus:
>
> > > CREATE OR REPLACE TRIGGER ibr_prdc
> > > BEFORE INSERT ON PERIODICAL FOR EACH ROW
> > > DECLARE
> > > ISBNo PERIODICAL.ISBN%TYPE;
> > > tnr PERIODICAL.PERIODICAL#%TYPE;
> > > tnre PERIODICAL.PERIODICAL#_OWNER%TYPE;
> > > BEGIN
> > > Select ISBN, PERIODICAL#,PERIODICAL#_OWNER into ISBNo,tnr,tnre
> > > FROM PERIODICAL Where ISBNo =:New.ISBN And tnre = :New.PERIODICAL#;
> > > IF ISBNo = :New.ISBN And
> > > tnre <>:New.PERIODICAL# THEN
> > > RAISE_APPLICATION_ERROR(-20070, 'ERROR>>' ||
> > > ' PERIODICAL:' ||
> > > ' For ISBN that already exists in the basen, it' ||
> > > ' must exist in periodicald to be accepted.');
> > > ELSE
> > > :New.ISBN := :New.ISBN;
> > > END IF;
>
> > > Daniel, when I use BEFORE INSERT I get:
> > > ORA-01403: no data was found
> > > ...
>
> > > When I use AFTER INSERT I get:
> > > ORA-04091: The table PERIODICAL is muteting, ...
>
> > > The trigger is not reporting any errors when compiled.
> > > ..
>
> > > Thank you guys for the help:)
>
> > > --
> > > Me
>
> > Daniel didn't explain that, I did. You need to get a count(*) of the
> > records currently in the table and process based upon that result.
> > Presuming the count(*) is 0 then you should be bypassing all of the
> > conditional logic in the trigger. This will 'fix' your 'no data
> > found' issue.
>
> > David Fitzjarrell- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks David.
>
> Yes, it was you who noticed my mistake in the number of columns. I
> erroniously said it was David and I'm sorry for that.
>
> I've used a count(*) as you suggested above and it actually 'fixed'
> the 'no data found' issue. However, it also ignored this check:
> ISBNo =:New.ISBN And tnre = :New.PERIODICAL#.
>
> I inserted an article with an existing ISBN and it was accepted
> without the condition on PERIODICAL#_OWNER being fulfilled.
>
> DECLARE
> ISBNo PERIODICAL.ISBN%TYPE;
> tnr PERIODICAL.PERIODICAL#%TYPE;
> tnre PERIODICAL.PERIODICAL#_OWNER%TYPE;
> cnt number(9);
> BEGIN
> Select count(*) into cnt
> FROM PERIODICAL Where ISBNo =:New.ISBN And tnre
> = :New.PERIODICAL#;
> IF ISBNo = :New.ISBN And
> tnre <>:New.PERIODICAL# THEN
> RAISE_APPLICATION_ERROR(-20070, 'ERROR>>' ||
> ' PERIODICAL:' ||
> ' For ISBN that already exists in the basen, it' ||
> ' must exist in periodicald to be accepted.');
> ELSE
> :New.ISBN := :New.ISBN;
> END IF;
>
> Thanks:-)
>
> --
> Me- Hide quoted text -
>
> - Show quoted text -

It isn't ignoring the condition, the value for the PERIODICAL#_OWNER is likely NULL, a condition you have yet to address. I also mentioned this in my response.

David Fitzjarrell Received on Tue Nov 20 2007 - 10:30:28 CST