> 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
and update Table A=> fields=>average_sellingprice with totalprice/totalsold
and
total_out with amount of sold

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

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.

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


Metin Gonen



---
avast! Antivirus: Giden mesaj temiz.
Virus Veritabani (VPS): 0525-5, 25.06.2005
Test zamani: 25.06.2005 21:25:33
avast! - copyright (c) 1988-2004 ALWIL Software.
http://www.avast.com