Re: simple trigger problem ?

Norab,

You apparently had to have thought this through to handle the situation when an address changes that all members of that residence move also. Now this may have been what you origionally wanted, but after further evaluation turns out not to be the case. You have to decide if it truly is the case. I would expect that it is, but this design does pose a problem, the one of course that is the topic of this discussion.

Let's look at the logic. You have a customer(s) that have 1 address. A customer wants to change their address.
First you have to find out if any other customers have that same address. That can be done by checking the customer table for all customers that have that same addressid. If there is more than one, then you would have to create a new address entry and associated it with the current customer that wants changed.

There is still the opposite situation where you have several members of one residence, let's say a business, and that address changes. Most likely all members of that company move with the address change. You will need to be able to handle that situation also, and currently that is the best case scenario for your current design, but again there has to be some knowledge that this is the desired effect, and not the latter. This can't be programmed into any application, it must be asked of the customer.

Now to solve the real problem, since a trigger can't query itself, then there won't really be a way to detect if there are any other customers that have the same address if you placed the trigger on the customer table. If you tried to place the trigger on the address table, then you would still get a mutating trigger through the referencial constraint back to the address table, when you went to associate the customer to the newly created address, that is assuming that you were able to pass that information back to a trigger, which you can't.

Basically, I can think of several way's to programatically handle this situation, but it isn't possible to do so with a trigger. Easy or not, this has to be done through some sort of form where the desired effect is an option in the form presented to the user supplying the change of address.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
"norab" <norab_at_digibel.org> wrote in message
news:XyBR4.171671$uF1.380194_at_afrodite.telenet-ops.be...


> 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


> 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


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

>

>