Adam wrote:

>"Lock conflict on no wait transaction" is not an error. It is an
>exception that is raised when a transaction tries to update or delete
>a record that has been changed by another transaction since the
>transaction was started.
>
>
>

Yes, of course. However, it is an error in the context of the
application.

>Hmmm, are multiple transactions trying to update the same cash draw
>record possibly?
>
>
>

Not a chance.

>No, it is two records that cause this.
>
>
>

Hmmmm. ok...

Your example below doesn't occur. I have no triggers for inventory
counts. it is simpler, and cleaner.

Here is a somewhat simplified explanation

Till (ID PK, Computed_Total, Tax_Total, counted_Total)
Transact ( ID PK , Till_ID FK, amount_tendered, Item_total, tax,
TaxRateApplied)
TransDetail (ID PK, Transact_ID FK, Item_Price)
Payment (ID PK, transact_ID FK, PMTMethod_ID FK, Amount)

the following triggers are of interest.
TransDetail Before insert, Update (left off the delete portion for
readability, as that can not occur in this context)
Begin
-- recalc total updating the transact row with all children including
this one

Select count(*), Sum(Item_Price)
from transdetail
where transact_ID=new.Transact_Id
into :Tcount, :tsum;

if (:tcount>0) then
begin
update Transact
set item_total=:Tsum,
Tax=:Tsum*TaxRateapplied
where ID=new.Transact_Id;
end
end

subsequently.

a trigger on transact before insert, update
AS
declare variable Atsum numeric(18,2);
declare variable Taxsum numeric(18,2);
begin
/* calculate computedBal for till */
Select Sum(Amount_Tendered), Sum(Tax)
from Transact
where Till_ID=new.Till_Id
into :ATSum, TaxSum;

Update Till
set Computed_Total=:AtSum,
Tax_Total=:Taxsum
where ID=new.Till_ID;

end

As you can see, there is no oportunity for two clients to be updating
information on the same row. i.e. inventory balances.

>You need to use another methodology for maintaining counts and totals
>if they are not serialised. The following link explains how a count
>can be made simultaneous transaction safe, it is not too hard to
>adjust the concept to total cash draws or inventory counts.
>
>http://groups.yahoo.com/group/firebird-support/message/56457
>
>
>
>

Yes, of course. I am aware of that. in fact, I had contributed to the
very message you reference above pointing out that the count(SomeColumn)
would exclude counting rows where Somecolumn was null. The Alexander
finished it up.

However, I am not trying to do anything of that nature.

Each person is issued their own till. Therefore, all transactions point
to that till, and all transaction details and payments point to those
transactions. Therefore, no chance of two users updating the same row
at the same time or otherwise, for that matter. If they did, I would
have had much bigger problems!

Adam, I thank you for taking the time to offer an answer...

Any other ideas?

Mitch