<mowinom_at_hotmail.com> schreef in bericht
news:6964aacf-80ed-4238-bf83-02af250171e7_at_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
There's a new set of errors in your statment:
Select count(*) into cnt
> FROM PERIODICAL Where ISBNo =:New.ISBN And tnre
> = :New.PERIODICAL#;
> IF ISBNo = :New.ISBN
It should be ( I think)
select count(*) into cnt
from periodical where ISBN = :NEW.ISBN etc, -- So not ISBNo, that one
isn't initialized!
same goes for trne!
and if cnt <> 0 then etc
Furthermore, your statement
> :New.ISBN := :New.ISBN;
is obsolete.
Shakespeare
Received on Wed Nov 21 2007 - 09:05:42 CST