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

Please don't post screenshots of textual information, but post error messages as code-formatted text. That improves readability and discoverability. — Mark Rotteveel, Feb 21 '21 at 14:48
@MarkRotteveel you mean delete error message screen and post only sqlcode and sqlstate of this error message? — basti, Feb 21 '21 at 14:53
No, post the error message as **text**, not as a screenshot (use the Copy button). — Mark Rotteveel, Feb 21 '21 at 14:57

score 2 · Accepted Answer · answered Feb 21 '21 at 14:57

2

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).

answered Feb 21 '21 at 14:57

Mark Rotteveel

100,966
191
140
197

Mark Rotteveel, BEFORE UPDATE gets the same error. As I understand it's impossible update the same table with update trigger. I change it with sp and it works properly. – basti Feb 21 '21 at 19:24
Mark Rotteveel, you say: "the select you perform doesn't need to select from FACTGAD, assuming you have a foreign key constraint between the two". Yes, I have foreign constraint but you mean delete select statement in trigger, before "update factgad set..." ? If I delete select statement how then initialize variables? – basti Feb 21 '21 at 19:31
I forgot to say that I drop trigger factgad_AU and RECGAD_AU works fine. :) Thank you, – basti Feb 21 '21 at 19:56
@basti As to your first comment, my suggestion was to use a `BEFORE UPDATE` combined with updating through the `NEW` context, and not do a direct table update. As to your second comment: I did not say you should not select, I just say that you only need to select from `recgad`, the join you do to `factgad` is unnecessary. – Mark Rotteveel Feb 22 '21 at 07:06
Mark Rotteveel, I don't understand what do you mean saying: " combine with updating through the NEW context". I change after update with before update, change select ( remove join fo factgad). Code now looks like: 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 where r.factgad_k=new.factgad_k group by r.factgad_k into :factgad_k,:NEWL, :OLDL, :NEWD, :OLDD; update factgad set new.NEWL=:NEWL, new.OLDL=:OLDL, new.NEWD=:NEWD, new.OLDD=:OLDD where factgad_k=:FACTGAD_K; but the same looping. :( – basti Feb 22 '21 at 13:43
1

@basti In a trigger that fires on the table `factgad`, you should **not** use `update factgad`, as that will fire the trigger again, and again, and again. Instead, assign the values to the columns of the `NEW` context using assignment statements (e.g. `NEW.OLDDD = :OLDD;`, etc.). – Mark Rotteveel Feb 22 '21 at 13:53
Mark Rotteveel, I understood. It works. thank you very much – basti Feb 22 '21 at 16:10

score 0 · Answer 2 · answered Feb 21 '21 at 20:17

0

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.

answered Feb 21 '21 at 20:17

user13964273

1,012
1
4
7

there were discussed very smart strategies for keeping aggreagate in multi-level tables (like, old aggregates - yearly, last two years monthly, etc). But perhaps that is worth it only on very large data. Personally i ended up with "lazy eval" solution: 1) modifyng details table removes data, that is setting master's column to `NULL`, 2) query checks if there is NULL and if there is runs the calculating SP. Granted, reads are much less often than writes there, and even less probable is overlapping of rows. In the end, i don't really need intermediate data until `select`. Just "flush the cache" – Arioch 'The Feb 21 '21 at 21:46

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

2 Answers2