Re: simple trigger problem ?

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)...

1. 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 CDT