Re: Conditional Trigger

From: <mowinom_at_hotmail.com>
Date: Tue, 20 Nov 2007 08:27:41 -0800 (PST)
Message-ID: <6964aacf-80ed-4238-bf83-02af250171e7@b15g2000hsa.googlegroups.com>

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

Received on Tue Nov 20 2007 - 10:27:41 CST