triggers - 如何使用更新触发器获取主表中详细表字段的总和

Question

Firebird 3.0 数据库中有主表“factgad”和详细表“recgad”。
factgad : factgad_k(pr_k)...
recgad : recgad_k(pr_k), factgad_k(fk)...

当我更新主表时，我必须获取明细表的记录总和，但我无法编写正确的代码。当我尝试更新主表时，出现错误:

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

触发器 SQL 有什么问题？我尝试更改 where 子句 where f.factgad_k=new.factgad_k与 where f.factgad_k=43但出现同样的错误。重新启动 Firebird 服务没有任何改变。
奇怪的行为，更新触发器后出现相同的错误，该触发器在更新明细表后使用明细表的总和更新主表:

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

您正在尝试更新表 FACTGAD在表更新时触发的触发器中 FACTGAD .换句话说，更新触发触发器，哪个更新，哪个触发触发器等等。这最终会触发错误“同一请求的并发执行太多”。
你不应该使用 UPDATE <table>在触发同一个表更新的触发器中。相反，您应该使用 BEFORE UPDATE触发器，并将更新的值分配给 NEW 的列语境。在 BEFORE UPDATE触发 INSERT或 UPDATE ，修改NEW上下文将更新要插入或更新的行。但是，在主表上触发的触发器中重新计算明细表中的值的总和没有多大意义:考虑如果主表从未更新但添加、删除或更新明细行会发生什么情况。
至于你的第二个触发器，我只能猜测你仍然在FACTGAD上有触发器。到位，或者您有其他触发器导致 FACTGAD 之间的更新循环和 RECGAD .
顺便说一句，您执行的选择不需要从 FACTGAD 中进行选择。 ，假设您在两者之间有外键约束。
TLDR:放下触发器 factgad_AU ，保留触发器RECGAD_AU (但考虑使其成为 AFTER INSERT OR UPDATE )。