Re: Conditional Trigger

On Nov 20, 5:30 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

You're absolutely right. I left the PERIODICAL#_OWNER value blank to test the trigger and the trigger responded with silence. Let me figure out this hill.

--
Me