Re: simple trigger problem ?

[Quoted] this is indeed the way the design is made AdrID is a foreign key in customers.

Why i did this is because i thought it demonstrated the theory behind an rdbms (its a project at university), and that storing 2 adresses twice or more would violate that theory.

The second solution you give seems the best to me, problem is that i cannot [Quoted] use oracle developer and MUST use oracle designer (school...sigh) and in designer it is not so simple to create advanced forms.

Thanx for your time
im looking in to it.
(im trying to understand your first solution, im not an expert, when and how [Quoted] exactly would these stored functions be triggered?)

greets
norab.

<rtproffitt_at_my-deja.com> wrote in message news:8f6ncn$cha$1_at_nnrp1.deja.com...
> Norab,
>
> It is not clear what your table relationship is....
> You are saying there is a many-to-one, but what is the
> key? It can't be customer ID, because then you would have
> multiple address records... So it must be something like
> AddressID
> Customer, CustID, AddrID .......
> AddrID, Address, city, state, zip, etc.....
>
> I wonder what led to this original design...?
> Are there so many addresses that there is a real big
> savings normalizing this way? Or is the size small enough
> that denormalizing the address (into the customer table)
> would give a big savings in maintenance, coding, etc.???
>
> All of that said... here are some ideas....
> (I will assume there is an AddressID field)...
>
> a) Create a stored function or functions which would
> handle all the possibilities...make a truth table:
> -first time cust insert, address does not exist
> -first time cust insert, address does exist
> -delete cust, address not used by others
> -delete cust, address used by others
> -update, old address no longer used, new address first time
> -update, ...ditto..., new address already used
> -update, old address still used, new first time
> -update, old still used, new already used.
> You will need a series of Select count, or cursors
> to gather the data you will need to answer the questions...
>
> Once you find which condition applies, then you can
> easily decide what to do:
> - Keep old address, insert a new address record,
> update cust with new AddrID.
> - Keep old address, just use it, update cust with AddrID
> - ... etc ...
>
> b) Intercept the normal functioning of Forms Insert and Update
> and Delete... you may have to convert everything to a control
> block ???...At the very least, you will need to trap with
> Forms triggers for Pre-Update, Pre-Insert, etc....
> Call the stored function and pass Both cust and Address...
> this gets around the problem of a table trigger not knowing
> whose address is being dealt with.
>
> c) Now, you hav a new problem with COMMIT.... you have just done
> some work in the back end, but the front end is now out of
> sync and does not know any work was done, so will not COMMIT
> properly..... either FORCE a block to be dirty so an Update
> will occur and Forms commit will translate into SQL COMMIT
> (.... x := x; ... would force update of same field to itself)
> or... perform a COMMIT in the back end, and then make sure
> to refresh and resync everything in the front end.
>
> No matter what, it is not trivial....
> Weigh this against changing the design of the address table,
> and asking yourself...was there a good purpose in the
> beginning for the original design...
>
> (Comments anyone? other suggestions?)
>
> HTH
> Robert Proffitt
> RTProffitt_at_Beckman.com
>
>
> "norab" <norab_at_digibel.org> wrote:
> > as a project i developed a simple database with oracle designer,
 including
> > application forms.
> > Here is what the problem is:
> >
> > a table customer is linked to a table adress with a many to one
> > relationship.
> > when i update a customer's home adress via the form, the adress of
 other
> > customers who live at the same adress is also changed.
> > off course because if 3 customers live at the same adress, only one
 adress
> > record will be in the adress table. and if one of those three
 customers
> > change adresses, and i update that record, the two other customers
 will also
> > have that changed adress.
> >
> > I was looking for a solution with database triggers (after update on
> > adressen), but the problem is that at that point i dont know WHICH
 customer
> > changed adress.
> >
> > Is there a solution with database triggers or any other?
> > tnx for al help
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon May 08 2000 - 00:00:00 CEST