PostgreSQL触发器函数动态更新外键遇栈深度超限问题求助

问题根源 #

你的触发器函数存在两个核心问题：

• 无限递归触发：如果触发器绑定在orders表上，函数内执行UPDATE orders会再次触发同一触发器，形成无限循环，最终导致栈深度超限。
• 逻辑错误：sellerId是关联staff.id的外键，你却试图将staff.reference_number赋值给它，既不符合外键关联规则，也达不到匹配关联的目的。

正确解决方案 #

行级BEFORE触发器可以直接修改NEW对象的字段值，无需执行额外的UPDATE语句，从根源避免递归触发。同时修正关联逻辑，通过reference_number匹配获取staff.id来赋值给sellerId。

修正后的触发器函数 #

CREATE OR REPLACE FUNCTION update_orders_seller_id() RETURNS TRIGGER AS $$
BEGIN
    -- 根据reference_number匹配staff表，获取对应的主键id赋值给sellerId
    SELECT staff.id INTO NEW.sellerId
    FROM staff
    WHERE staff.reference_number = NEW.reference_number;
    
    -- 可选：未匹配到对应员工时的处理逻辑，比如抛出异常或设为NULL
    -- IF NEW.sellerId IS NULL THEN
    --     RAISE EXCEPTION 'No staff found with reference_number: %', NEW.reference_number;
    -- END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器 #

将触发器绑定到orders表的插入、更新事件：

CREATE TRIGGER trigger_update_orders_seller_id
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_orders_seller_id();

关键说明 #

• 使用BEFORE触发器直接修改NEW对象，PostgreSQL会自动将修改后的NEW写入表中，不会额外触发UPDATE操作，彻底避免递归。
• 确保staff.reference_number字段唯一，否则SELECT ... INTO会因返回多行报错，可根据实际情况添加LIMIT 1或为该字段创建唯一约束。

内容的提问来源于stack exchange，提问作者Evaldas Sedys