PostgreSQL 12：如何在更新fk_child时触发fk_parent的触发器

针对你的问题，我有几个实用的方案，都是基于PostgreSQL 12的特性设计的，既能触发目标逻辑，又能尽量减少触发器的执行次数：

方案1：通过父表的“触发字段”间接触发原触发器 #

这个思路是给fk_parent新增一个专门用于触发更新的字段，当fk_child数据变化时，更新对应父表的这个字段，从而触发你已有的after update触发器。

步骤： #

1. 给父表添加触发字段

ALTER TABLE fk_parent ADD COLUMN last_child_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW();

这个字段用来标记子表的最后更新时间，不会影响原有业务逻辑。

2. 创建子表的触发器（优化版：批量减少触发）
   如果用FOR EACH ROW的触发器，同一个父表的多个子行更新会多次触发父表更新，所以推荐用FOR EACH STATEMENT的批量处理版本：

CREATE OR REPLACE FUNCTION trg_child_update_parent_signal()
RETURNS TRIGGER AS $$
BEGIN
    -- 批量更新所有受影响的父表，每个父表仅更新一次
    UPDATE fk_parent p
    SET last_child_updated = NOW()
    WHERE EXISTS (
        SELECT 1 FROM fk_child c
        WHERE c.fk_parent_id = p.id
        AND (
            (TG_OP IN ('UPDATE', 'DELETE') AND c.id IN (SELECT id FROM OLD))
            OR (TG_OP = 'INSERT' AND c.id IN (SELECT id FROM NEW))
        )
    );
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_fk_child_signal_parent
AFTER INSERT OR UPDATE OF amount OR DELETE ON fk_child
FOR EACH STATEMENT EXECUTE FUNCTION trg_child_update_parent_signal();

这样不管一次操作修改多少个子行，每个关联的父表只会被更新一次，大大减少触发次数。

方案2：提取同步逻辑为独立函数，父子表触发器复用 #

这个方案更直接，把你原触发器里“删除另一模块数据并重新插入新内容”的核心逻辑提取成独立函数，然后分别在父子表的触发器里调用它，精准处理受影响的父表。

步骤： #

1. 提取同步逻辑到独立函数

CREATE OR REPLACE FUNCTION sync_external_module(p_parent_id INT)
RETURNS VOID AS $$
BEGIN
    -- 这里替换成你原触发器里的业务逻辑
    -- 示例：删除旧数据，插入包含子表的新数据
    DELETE FROM external_module WHERE parent_id = p_parent_id;
    
    INSERT INTO external_module (parent_id, total_amount, child_records)
    SELECT
        p.id,
        p.amount + COALESCE(SUM(c.amount), 0),
        ARRAY_AGG(JSON_BUILD_OBJECT('child_id', c.id, 'amount', c.amount))
    FROM fk_parent p
    LEFT JOIN fk_child c ON p.id = c.fk_parent_id
    WHERE p.id = p_parent_id
    GROUP BY p.id, p.amount;
END;
$$ LANGUAGE plpgsql;

2. 修改原父表触发器，调用新函数

CREATE OR REPLACE FUNCTION trg_parent_sync()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM sync_external_module(NEW.id);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 替换你原有的父表触发器
CREATE TRIGGER trg_fk_parent_sync
AFTER UPDATE ON fk_parent
FOR EACH ROW EXECUTE FUNCTION trg_parent_sync();

3. 创建子表的批量同步触发器

CREATE OR REPLACE FUNCTION trg_child_sync_parent()
RETURNS TRIGGER AS $$
DECLARE
    v_parent_ids INT[];
BEGIN
    -- 收集所有受影响的父表ID（去重）
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        SELECT ARRAY_AGG(DISTINCT fk_parent_id) INTO v_parent_ids FROM NEW;
    ELSIF TG_OP = 'DELETE' THEN
        SELECT ARRAY_AGG(DISTINCT fk_parent_id) INTO v_parent_ids FROM OLD;
    END IF;

    -- 批量同步每个父表
    IF v_parent_ids IS NOT NULL THEN
        FOREACH pid IN ARRAY v_parent_ids LOOP
            PERFORM sync_external_module(pid);
        END LOOP;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_fk_child_sync_parent
AFTER INSERT OR UPDATE OF amount OR DELETE ON fk_child
FOR EACH STATEMENT EXECUTE FUNCTION trg_child_sync_parent();

方案对比与优化建议 #

• 方案1：优点是不需要修改原触发器逻辑，仅需新增字段和子表触发器；缺点是会产生父表字段的额外更新。
• 方案2：优点是逻辑更清晰，无冗余字段更新，复用性更强；缺点是需要调整原触发器的结构。

额外优化：可以给子表触发器加上WHEN条件，只在amount字段变化时触发，避免无关字段更新导致的无用触发：

CREATE TRIGGER trg_fk_child_sync_parent
AFTER UPDATE ON fk_child
FOR EACH ROW
WHEN (OLD.amount IS DISTINCT FROM NEW.amount)
EXECUTE FUNCTION trg_child_sync_parent();

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