Subject Re: [firebird-support] Foreign Key on update and after update trigger
Author Svein Erling Tysvær

No, it does not seem difficult, Olaf. Assuming that twarehouse_sum contains some summation of twarehouse, I'd say the reason is as simple as two separate transactions modifying twarehouse simultaneously in a way that makes the change of the other transaction invisible.

Generally, I'd say the idea of deleting and inserting into a summation table in a trigger on a detail table is something that works well only with maximum one concurrent user per row of the summation table. It simply contradicts concurrency!

What I'd rather recommend, is that the trigger always inserts into the summation table, but never deletes from it. If you insert a new record into twarehouse, the trigger should insert +1 into twarehouse_sum, if you delete a record from twqarehouse, the trigger should insert -1 into twarehouse_sum (change +1/-1 appropriately if the summation table contains sums rather than counts). Then you should have a separate process that at set times (e.g. once every hour, day or week), does something like:

for select DistinctField, sum(MySummationField) from twarehouse_sum group by 1
    into :DistinctField, :MySum do
  delete from twarehouse_sum where DistinctField = :DistinctField;
  insert into twarehouse_sum(DistinctField, SummationField)
  values(:DistinctField, :MySum);

That would require you to generally change all your
  select DistinctField, MySummationField
  from twarehouse_sum


  select DistinctField, sum(MySummationField)
  from twarehouse_sum
  group by 1

whenever you want to find the sum, but it is scalable (i.e. a lot better for concurrency) and avoids the problem you've observed.