How to get sum of detail table fields in master table with update trigger

Question

There are master table 'factgad' and detail table 'recgad' in a Firebird 3.0 database.

factgad: factgad_k(pr_k)...

recgad: recgad_k(pr_k), factgad_k(fk)...

When I update the master table, I have to get sum of detail table's records, but I couldn't write the right code. When I try to update master table, I get error:

Error Message:
----------------------------------------
Too many concurrent executions of the same request.
Too many concurrent executions of the same request.
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, col: 3
At trigger 'FACTGAD_AU' line: 21, co...
---------------------------------------------------
SQLCODE: -693
SQLSTATE: 54001
GDSCODE: 335544663



create or alter trigger factgad_AU FOR factgad
ACTIVE AFTER UPDATE POSITION 0
AS
DECLARE VARIABLE newl DECIMAL(8, 4);
DECLARE VARIABLE oldl DECIMAL(8, 4);
DECLARE VARIABLE newd DECIMAL(8, 4);
DECLARE VARIABLE oldd DECIMAL(8, 4);
DECLARE VARIABLE FACTGAD_K INTEGER;
BEGIN
 select
        f.factgad_k,
        sum(r.fnewl*r.rgad),
        sum(r.foldl*r.rgad),
        sum(r.fnewd*r.rgad),
        sum(r.foldd*r.rgad)
   from recgad r, factgad f
   where f.factgad_k=new.factgad_k and r.factgad_k=f.factgad_k
   group by f.factgad_k
   into  :factgad_k,:NEWL, :OLDL, :NEWD, :OLDD;

  update factgad set
    factgad.NEWL=:NEWL,
    factgad.OLDL=:OLDL,
    factgad.NEWD=:NEWD,
    factgad.OLDD=:OLDD
   where factgad_k=:FACTGAD_K;
end

What is wrong in trigger SQL? I tried change in where clause where f.factgad_k=new.factgad_k with where f.factgad_k=43 but the same error appears. Restarting of Firebird service nothing changed.

Strange behavior, the same error appears in after update trigger which updates master table with sums of detail table after updating detail table:

CREATE OR ALTER TRIGGER RECGAD_AU FOR RECGAD
ACTIVE AFTER UPDATE POSITION 0
AS
DECLARE VARIABLE NEWL DECIMAL(8, 4);
DECLARE VARIABLE OLDL DECIMAL(8, 4);
DECLARE VARIABLE NEWD DECIMAL(8, 4);
DECLARE VARIABLE OLDD DECIMAL(8, 4);
DECLARE VARIABLE FACTGAD_K INTEGER;
begin
 select
        r.factgad_k,
        sum(r.fnewl*r.rgad),
        sum(r.foldl*r.rgad),
        sum(r.fnewd*r.rgad),
        sum(r.foldd*r.rgad)
   from recgad r, factgad f
   where r.factgad_k=new.factgad_k and r.factgad_k=f.factgad_k
   group by r.factgad_k
   into  :factgad_k,:NEWL, :OLDL, :NEWD, :OLDD;

  update factgad set
    factgad.NEWL=:NEWL,
    factgad.OLDL=:OLDL,
    factgad.NEWD=:NEWD,
    factgad.OLDD=:OLDD
   where factgad_k=:FACTGAD_K;
end 

Answer 1

You are attempting to update the table FACTGAD in a trigger that fires on updates of the table FACTGAD. In other words, the update fires the trigger, which updates, which fires the trigger, etc. etc. This will eventually trigger the error "Too many concurrent executions of the same request".

You should not use UPDATE <table> in triggers that fire on updates of that same table. Instead, you should use a BEFORE UPDATE trigger, and assign the updated values to the columns of the NEW context. In a BEFORE UPDATE trigger on INSERT or UPDATE, modification of the NEW context will update the row to be inserted or updated. However, recalculating a sum of values from detail tables in a trigger that fires on the master table doesn't make much sense: consider what happens if the master table is never updated, but detail rows are added, deleted or updated.

As to your second trigger, I can only guess that you still had the trigger on FACTGAD in place, or you have other triggers that cause a cycle of updates between FACTGAD and RECGAD.

As an aside, the select you perform doesn't need to select from FACTGAD, assuming you have a foreign key constraint between the two.

TLDR: drop the trigger factgad_AU, retain the trigger RECGAD_AU (but consider to make it a AFTER INSERT OR UPDATE).

Answer 2

Generally speaking it is impossible to get sum of detail records in master table reliable. The best approach is adding and subtracting data by triggers on detail table but under load it end up in update conflicts. Your trigger will end up in completely wrong values in concurrent environment.

Usually calculation of aggregates in select performs well enough.