如何避免PostgreSQL触发器在UPDATE操作时出现递归问题？

你的触发器在添加UPDATE触发条件后出现递归循环，本质是触发器内的UPDATE contracts操作会再次触发同一个触发器，形成无限调用。下面提供两种可行的解决思路：

方法一：限定触发器触发的字段范围 #

既然只有sum字段变化时才需要重新计算nmck_decrease_percent，可以直接在触发器定义中指定仅当sum字段被更新时触发，这样触发器内更新nmck_decrease_percent的操作就不会再次触发自身。

修改触发器定义：

CREATE OR REPLACE TRIGGER trig_percent_calc
     AFTER INSERT OR UPDATE OF "sum" ON contracts
     FOR EACH ROW
     EXECUTE PROCEDURE nmck_decrease_percent_calc();

如果需要更灵活的判断（比如处理sum字段为null的场景），可以用WHEN子句：

CREATE OR REPLACE TRIGGER trig_percent_calc
     AFTER INSERT OR UPDATE ON contracts
     FOR EACH ROW
     WHEN (TG_OP = 'INSERT' OR NEW."sum" IS DISTINCT FROM OLD."sum")
     EXECUTE PROCEDURE nmck_decrease_percent_calc();

方法二：通过触发器深度判断避免递归 #

PostgreSQL提供了pg_trigger_depth()函数，用于获取当前触发器的调用深度。第一次触发时深度为1，递归触发时深度会大于1，我们可以在函数中加入判断，只在深度为1时执行更新逻辑。

修改触发器函数：

CREATE OR REPLACE FUNCTION nmck_decrease_percent_calc() RETURNS TRIGGER AS
$BODY$
DECLARE
        s_price integer;
BEGIN
    -- 跳过递归触发的情况
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;
    END IF;

    SELECT "lotMaxPrice" into s_price FROM lots WHERE "purchaseNumber" = new."purchaseNumber";

    UPDATE contracts SET nmck_decrease_percent = (100 - round(( (new.sum::numeric/s_price::numeric) * 100), 4 ))
      WHERE "purchaseNumber" = new."purchaseNumber" AND "lotNumber" = new."lotNumber";

    RETURN new;
END;
$BODY$
language plpgsql;

之后保持触发器的AFTER INSERT OR UPDATE定义即可：

CREATE OR REPLACE TRIGGER trig_percent_calc
     AFTER INSERT OR UPDATE ON contracts
     FOR EACH ROW
     EXECUTE PROCEDURE nmck_decrease_percent_calc();

内容的提问来源于stack exchange，提问作者Dmitry Bubnenkov