Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: simple trigger problem ?
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