--- In firebird-support@yahoogroups.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