Metin Gönen wrote:

>
>
>>I'm not sure we have got your sequence right here.
>>Changes to Table B result in the need to update the count and average
>>for a particular record in Table A?
>>So Table A ideally needs fields for Total and Count (Avr = total/count)
>> and the trigger on Table B just adds to or decreases the Total and
>>Count as required?
>>After updating Table B, we need a refresh to update any display based on
>>Table A.
>>At the moment I can't see why you need to be 'locking' Table A except to
>>write the update. So the problem may be that you need updates to TableA
>>in a different transaction to TableB.
>>What am I missing here?
>
> I will write another example to show what the trouble is
>
> I have Table A =>fields
> (generated_number,stock_id,stock_code,stock_name,average_sellingprice,total_
> in,total_out)
> I have table B =>fields (
> generated_number,stock_id,selling_price,amount_of_sold)
>
> I relate these two tables with the stock_id fields.
>
> What I was doing is when I add a new record to Table B
> The TableB_after_insert_trigger fires and sums all the records in
> tableB=> sum(selling_price*totalprice) as TotalPrice , sum(amount_of_sold)
> as totalsold

Kill the sum() elements

Goods in -> total_in = total_in + stock_in

Goods out -> total_out = total_out + stock_out

( Stock Available = total_in - total_out )

> and update Table A=> fields=>average_sellingprice with totalprice/totalsold
> and
> total_out with amount of sold

Rather than average_sellingprice store total_cost_of_sales and update
that by adding the new cost_of_sale. The average_sellingprice is then
simply total_cost_of_sales/total_out and does not involve doing a sum
every time you add a sale.

> while the trigger tries to sum all the records in TableB a Lock Conflict
> occurs.
> This is the main problem.

By not having to visit any existing records, there should not be a conflict.

> If I only want to get the total amount of sold then your suggestion of
> inc/decr will be ok.
> But I need the average selling price so I have to look at all the recors.
> While I am looking all the records I also
> sum the ins/outs also.

But you know if you are increasing or decreasing the amounts - and
increasing the total_cost_of_sales (or decreasing if making a correction)

> Now ; Is there a way to write average_selling prices gathered from tableB to
> somewhere when I update/insert a record in TableB ?

Simply by keeping track of the total_cost_of_sales and the number sold.
You do not need to visit any records in TableB other than the one being
inserted. In fact, only the on_insert trigger is required, and will be
triggered when an order is committed. You should not normally be
deleting records in table B, but if you are, then the totals need to be
subtracted rather than added, and if you need to update an order,
normally I would cancel the existing order, and add a new one, so you
have a record of the changes via records in TableB with a cancelled flag
attached.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services