Metin Gönen wrote:

> At the end I decided to use master detail tables. I deleted the triggers.
> Now the master table shows the necessary details of stocks records and the
> detail table sums up the averages and the total ins and outs. but actually
> this is not the way I want. As I told before in this way I can't show all
> the results in one line as customers get used to. Also in some accounting
> programs it is almost impossible to use master detail relation. At this time
> I use queries to get the sums and averages. Also I write some event handlers
> to show the averages when displaying the master dataset in a grid. This is
> the worst of all because it really lowers the performance.

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?

> This is not a new thing. I know that most database programmers suffers
> because of this situation. What others are doing to solve this problem ?
> Especially on web database programming. if people would like to share their
> solutions it will help us to improve ourselves.

The trick in many cases is to get the 'engine' to do the work. I have a
table that carries a lot of transactional information, which the users
are continually reading - but NEVER write to. Only the triggers create
and update records. The original design prior to sorting this out had
all sorts of problems with 'dead-lock' just like you are seeing, but now
I don't have any ;)
Perhaps you need a third table with just the results in, and a primary
key of the TableA entry - that way the client never writes to the
results table, but I'm sure that things can be organised so that only
the two tables are needed.

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