| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: simple trigger problem ?
I put a lot of work in the following:
when i ADDED a customer with an adress that was already in the database, a new adress record would be created, so two identical adressen were stored. which i didnt want. A few database triggers now check after adding a customer if no doubles are in the adress table, if so removes one, and updates the customers foreign key.
i know a solotion to the problem described below is to only allow one
customer at an adress.
but i dont want that.
And i dont think a database is incorrectly designed when its possible to have 2 or more customers at the same adress with one adress record. (theory of many to one relationships?)
Could you describe the trigger solution more in detail ? my problem is that when i put a trigger on the adress table, which would insert a record instead of updating one. I cant know what customer foreign key needs to be changed to the new adress record.
X, Y, Z live at the same adress, three foreign keys points to adress_id 5 (for example)
X adr
adr_id: 5 id: 5
street: ......
Y
Z
adr_id: 5
in the form i update the adress of Z,
adres record with id 5 gets updated
a trigger intercepts that update and transforms it into an insert.
the foreign key of Z now needs to be changed to the new adress record.
(example 5 must become 6)
X en Y must remain the same.
but in that trigger, there is no way to know if its X, Y, or Z that needs to be changed right? The only thing i know is that the adress record gets updated. is it because of X,Y or Z... no way to know that.
sorry for the long mail.
kind regards
norab
"Mark D Powell" <markp7832_at_my-deja.com> wrote in message
news:8f6ltp$b16$1_at_nnrp1.deja.com...
> In article <CYzR4.170401$uF1.376960_at_afrodite.telenet-ops.be>,
> "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
> >
> With your current design if Customer A and B divorce and one of them
> places an order you will incorrectly update the address of the other to
> match. The same problem exist for multiple offices for one company,
> etc... As far as coding triggers to handle your updates I think you
> need to be a little more clear on what you want to accomplish. You can
> run into a mutating table problem if you try to code triggers on A to
> update B and tiggers on B to update A. On the other hand if the
> customer table only has a pointer to the address table then the change
> of the address will not require any change to A. In this case if you
> want to know who is affected by the address change just build an index
> on the FK address column in A and query on it.
>
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon May 08 2000 - 00:00:00 CDT
 |
 |