On Nov 21, 7:45 pm, Frank van Bortel <frank.van.bor... (AT) gmail (DOT) com>
wrote:
Frank, you're absolutely right. In my case, however, design is done by
other people and different aspects of development by others. And
unfortunately, I'm not in a position to demand anything.

Hi all,

I've modified the trigger and used a cursor. It's working fairly well
now. It'll accept a second ISBN in the ISBN field only if the value of
PERIODICAL#_OWNER being entered exists in the ISBN column. What I've
notice, however is that once entered, the PERIODICAL#_OWNER attribute
accepts any value entered including nulls when updated. Any suggestion
to better this is warmly welcome and appreciated.

David, you realize that this is not a school project. So, unconcealing
the code would help, a lot.

Here the code:

CREATE OR REPLACE TRIGGER temp_test
BEFORE INSERT OR UPDATE OF ISBN ON PERIODICAL
FOR EACH ROW WHEN (new.ISBN IS NOT NULL)
DECLARE
get VARCHAR2(9);
invalid_value EXCEPTION;
valid_value EXCEPTION;

CURSOR curGet (issno VARCHAR2) IS
Select ISBN FROM PERIODICAL
Where ISBN IN
(SELECT ISBN FROM PERIODICAL
WHERE PERIODICAL# = :new.PERIODICAL#_OWNER)
FOR UPDATE OF ISBN;
BEGIN

OPEN curGet (:new.ISBN);
FETCH curGet INTO get;

IF curGet%NOTFOUND THEN
RAISE invalid_value;
ELSE
RAISE valid_value;
END IF;
CLOSE curGet;

EXCEPTION
WHEN invalid_value THEN
CLOSE curGet;
RAISE_APPLICATION_ERROR(-20070, 'ERROR>>' ||
' PERIODICAL:' ||
' For ISBN that already exists in the basen, it' ||
' must exist in periodicald to be accepted.');
WHEN valid_value THEN
CLOSE curGet;

END;

mowinom (AT) hotmail (DOT) com wrote:
It isn't about demanding it is about being respected as a professional.

If you make a logical case and are rebuffed find a better job.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks Daniel.

I'll chew on them (the acm articles) one by one buy of now, let me
solve this current puzzle first.

Regards,
Mark


On Nov 21, 3:14 pm, DA Morgan <damor... (AT) psoug (DOT) org> wrote:

On Tue, 20 Nov 2007, mowinom (AT) hotmail (DOT) com wrote:

It sounds to me that the designs of the "too-many tabled" database
probably has quite a few really really poorly designed tables which
cause headache after headache. So, new poorly designed tables (of which
this one is) start compounding the problems. But, the real problem is
that the designer/s of the tables in this database never knew what they
were doing in the first place.

I guess if I was paying for it and I knew nothing about databases and
all I knew was that a new table equated to more problems, I'd be
inclined to keep the number of tables at a bare minimum as well.

--
Galen Boyer

You have my sympathy.

--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Hi All,

I solved the puzzle buy using two cursors. One cursor fetched all
records that equaled :New.ISBN and the other fetched records in the
periodical# column that equaled :New.periodical#_Owner.

After that, I used a conditional check to ensure that if the ISBN
being entered in the table already existed then periodical#_Owner was
to be provided and that it had to be equal an existing periodical#.

The trigger is working fine and I'm happy with it. Thanks to you all
for your suggestions and helpful criticism; special thanks to David
and Daniel.

Regards,
Mark

On Nov 28, 6:09 am, mowi... (AT) hotmail (DOT) com wrote:
You're welcome.


David Fitzjarrell