Re: simple trigger problem ?

Based on all the discussion, a many-to-many relationship is not the correct model. A given customer only has one address. A given address may apply to one or more customers.

Bob Fazio had the best reply so far, but I would go just a bit further and say that the problem probably comes from thinking of Customer as parent to Address. It is not; Address is the parent entity. A form query may start with a customer, but it has to be as a foreign key lookup in the Address table. Then display all the child Customers and determine which ones need the address change. If all are to be changed, then update the address record. If less than all are to be changed, then a new record must be inserted into the Address table.

Of course, there is the situation where the customer's new address is already in the Address table for another customer (a marriage?), but that would add more complexity. If the original query were on Address, however...

<rtproffitt_at_my-deja.com> wrote in message news:8f7m13$ght$1_at_nnrp1.deja.com...
> Norab,
>
> The theory is great for the classroom, but after that
> comes denormalization for a special purpose, such as
> speed, etc... This is different than blindly building
> a badly denormalized database...
>
> The stored proc option would work this way:
> (I'm not sure how you define it in Designer)
> Create or replace FixMyAddress(custId number, AddrId number) as
> PL/Sql....
> Cursor checkAddress is select.....etc
> Cursor checkcust is select ...etc
> example:
> Begin
> If ...theaddressdoesnotexist ....
> ... do the insert of new address ...
> else
> etc etc etc
>
> You would call it from Forms.
>
> Leonhard's description reminds of something else...
> Your design might be a good candidate for a
> junction table such as:
>
> Cust: CustID, name, type of cust, etc.
> Addr: AddrID, address, city, state, etc..
> Junction:
> customer-address: CustID, AddrID
>
> This would allow a many-to-many relationship.
>
> Good Luck,
> Robert Proffitt
> RTProffitt_at_beckman.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue May 09 2000 - 00:00:00 CDT