Hi guys,

Sorry for taking this thread out of the grave, but i have another doubt
about the same thing.

One of the ideas that poped up in this thread was to make a table for the
batch numbers and just insert values there, and write a procedure to clean
up this table once in a while (sum all the rows with the same batch number,
create just one with the total and after that delete all the old records).

After dirting my hands with code and hitting the head against the wall
severel times, now i think this would be the best way to do it.

But still i have a doubt.

Lets imagine that my table is filled with data, and i run the procedure to
sum the records of the same batch number and after that create just one row
with the total qualtity and then delete all the other records.

Wich would be the best way to do it? what if more users are using this table
when i m running this procedure? how can i prevent my data for being
inaccurrate?

Could you guys help me out with this?

Thanx in advance.

On 7/28/06, Adam <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com<firebird-support%40yahoogroups.com>,
> "Fabio Gomes" <fabioxgn@...>
> wrote:
> >
> > Ok, i ll simplify, i have 2 tables:
> >
> > General
> > id
> > Product_id
> > Quantity
> > Serial_number
> >
> > Serials
> > Serial_number(PK)
> > Product_id(FK)
> > Quantity
> >
> > What i need to do is, when i change something on the first table, if the
> > Serial_number field is filled with something i need to update the second
> > table, I.E.
> >
> > If i bought a product that had a serial number like: 123456 when i
> do this:
> >
> > Insert into General (Product_id, Quantity, Serial_number) VALUES (01, 1,
> > "123456");
> >
> > I need to check if the serial number 123456 is on the Serials table, if
> > there is then i need to add 1 to the quantity, if there isnt any serial
> > numbers like 123456, i need to add a new row with the value 1.
> >
> > I need to do it in any changes in the "general" table.
>
> The triggers on the general table to achieve this are very simple, but
> this is the sort of logic I was warning about.
>
> In the underlying trigger will be something like:
>
> update serials set quantity = quantity + 1 where .....
>
> if (ROW_COUNT=0) then
> begin
> insert into serials .....
> end
>
> Here is the problem, lets assume two different transactions want to
> add a record to General at the same time.
>
> Both transactions would be trying to update the same record in
> serials, and this would give you a lock conflict.
>
> Other possibilities are if no record existed in serials, so two
> transactions attempted to insert the same record into serials, one
> would no doubt receive an exception.
>
> In other words, your design makes it impossible to insert the same
> serial number into the general table at the same time. You must ask
> yourself if that is acceptable? Do you tell a customer that they can
> not buy a piece of stock yet because another customer is currently
> buying the same thing? Usually not.
>
> So if it must be possible for two different transactions to be
> inserting the same serial number in general at the same time, you need
> to approach this differently.
>
> At this point, I think it is important you take a look at Helen's post
> and implement her suggestions, because it seems wrong to me that you
> would have two fields to match between general and serials
>
> select *
> from general g
> join serials s on (g.serial_number = s.serialnumber and g.product_id =
> s.product_id)
>
> Seems like it requires normalisation.
>
> Your problem is quite similar to the problem of maintaining a record
> count, for which there are some clever ways to avoid lock conflicts.
> The same techniques can be used to gain the best of both worlds here,
> the ability to insert the same serial# and productid into general from
> two different simultaneous transactions AND the ability to count them
> quickly when required.
>
> Take a look at this post and see if you can adapt it to your need.
> http://groups.yahoo.com/group/firebird-support/message/74837
>
> (But get the normalisation fixed up first).
>
> Adam
>
>
>


[Non-text portions of this message have been removed]