Reputation: 431
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
Upvotes: 2
Views: 285
Reputation: 1221
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.
Upvotes: 0
Reputation: 108998
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
).
Upvotes: 2