Subject Re: [IBO] Not IBO related, but IB related
Author Flipado

Thank you very much for your reply. Here is the reason:

I have a main table for Products

table p products:
product part_number Primary Key
product catalogueCode (Not Unique)

other tables references Products by his Part_Number field.

I need to keep a table with CatalogueCode plus quantities. So, when
the user, for example, inserts products in stock, I use the catalogue
code to keep track of Quantities In Stock & Reservations.

(Imagine if they are Hard Disks. We can have HD 20 Gb Maxtor,
Seagate, etc. They have a distinct Part Number, but they all have a
CatalogueCode='HD20GB', for example).

I can not do it with joins because I don't know wich product will be
send to the client (HD example: I could not put reservations on the
product itself. Don't ask me why: there are reasons like StockGroups
of WareHouses, Serial Number Control, etc, etc, etc)

People use always the Part Number for In/Out products, but they use
catalogue to do reserves (In the hard disk example, imagine that the
clients don't bother about the brand, they just want to buy 20GB Hard

So, I must keep a table that refers to CatalogueCodes instead of Part
Numbers. I use triggers for updating this table. There are lots of
Tables that update this one (there are lots of tables that references
product part_number).

I want my user to be able to change the catalogue code of the
products that are not used anywhere.

Long explanation?

I find out a simple way to do it on the client side: Record a buffer
of the record and Delete it. No errors from the server? Ok, let's
fill a new record and recreate it with the new Catalogue Code. Error
from the server? The product is referenced elsewhere.

But I want (like) to have this kind of things on the server.

I will try with your ideia on the trigger before update tomorrow
(it's very late here in Portugal...) but I'm folowing something like:

(new.catalogue<>old.catalogue) ?
Delete the record that I'm updating
(Fire trigger for deleting)
Ok? Create a
Not Ok? Exception 'Could not change Catalogue'

hehehe. Let's try it.

Thanks to All!


--- In IBObjects@y..., Lucas Franzen <luc@r...> wrote:
> There is no count of references (I think this would slow down the
> database tremendously).
> You can lookup dependencies on your own in the system tables
> it will be very tricky to get the number of related records to a
> record that way.
> Why do you want to change the value at all?
> A primary and foreign key should have no other purpose than
> linking/joining records together thus qualifying them. It looks to
me as
> if you want to quantify them that way.
> If you really want to do it you could also try to change the value
> catch the db exception inside the trigger code with a WHEN ... DO
> construct and react in an appropriate way.
> Luc.
> Flipado schrieb:
> >
> > I hope some of you can help me on this. I have post it on mers,
> > no luck :(.
> >
> > This is the problem:
> >
> > (cut & Paste from my post in Mers)
> >
> > I have a problem that I don't know how to solve. I need to pick
> > info on
> > a trigger is the register that I'm updating is referenced
> > I will
> > try to explain myself:
> >
> > TABLE A:
> > Code PK
> > GroupCode
> >
> > TABLE B,C,D,....
> >
> > What I need to know is there any reference to TABLE A.CODE in any
> > the
> > other tables, so I can change safely the value of GroupCode.
> >
> > Is there any place in interbase that 'Counts' the number of
> > references that
> > I can use on a table?
> >
> > Thanks to All
> > (and sorry about my english)
> > (and sorry again for my off-topic message.
> > I use this list every day because I'm a (two-months) newbie to IB
> > IBO : I'm a listener to the for now, but I hope I can help in a
> > months)
> >
> > Artur Anjos
> >
> >
> >
> >
> > Your use of Yahoo! Groups is subject to