PostgreSQL触发器：确保my_table2与my_table1字段同步（不受插入顺序影响）

现有触发器的问题分析 #

你的当前实现存在几个关键缺陷：

• my_table2触发器的触发条件太窄：仅当a_column为空或空字符串时才同步，若my_table2先插入时a_column被设为非空值，后续my_table1插入对应行时无法触发同步。
• 未覆盖my_table1的更新场景：仅处理my_table1的插入操作，当my_table1的a_column更新后，my_table2不会同步更新。
• 笔误导致赋值错误：第一个触发器函数中INTO new.column应为INTO new.a_column，否则无法正确赋值。
• 批量操作可能遗漏：语句级触发器仅处理插入，但批量更新场景未覆盖。

优化后的触发器实现 #

以下是覆盖所有同步场景的完整方案：

1. 给两张表创建联合索引（提升性能）

确保(a,b)作为关联键有索引，避免触发器执行时全表扫描：

-- my_table1的联合唯一索引（确保(a,b)唯一，避免重复数据）
CREATE UNIQUE INDEX idx_my_table1_a_b ON my_table1(a, b);

-- my_table2的联合索引（加速关联查询）
CREATE INDEX idx_my_table2_a_b ON my_table2(a, b);

2. my_table2插入/更新时同步my_table1的值

无论a_column当前值是什么，都从my_table1拉取最新值：

CREATE OR REPLACE FUNCTION sync_my_table2_from_my_table1() RETURNS trigger AS
$$
BEGIN
    -- 从my_table1获取对应行的a_column，不存在则设为NULL
    SELECT a_column
    FROM my_table1
    WHERE (a, b) = (NEW.a, NEW.b)
    INTO NEW.a_column;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER sync_my_table2_trigger
BEFORE INSERT OR UPDATE ON my_table2
FOR EACH ROW
EXECUTE PROCEDURE sync_my_table2_from_my_table1();

3. my_table1插入/更新时同步到my_table2

当my_table1的a_column变化时，主动更新my_table2的对应行：

CREATE OR REPLACE FUNCTION sync_my_table1_to_my_table2() RETURNS trigger AS
$$
BEGIN
    -- 处理单条行的插入/更新
    UPDATE my_table2 t
    SET a_column = NEW.a_column
    WHERE (t.a, t.b) = (NEW.a, NEW.b);
    
    -- 处理批量插入（比如INSERT ... SELECT语句）
    IF TG_OP = 'INSERT' AND TG_LEVEL = 'STATEMENT' THEN
        UPDATE my_table2 t
        SET a_column = o.a_column
        FROM new_datas o
        WHERE (t.a, t.b) = (o.a, o.b);
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 行级触发器：处理单条插入/更新
CREATE OR REPLACE TRIGGER sync_my_table1_row_trigger
AFTER INSERT OR UPDATE ON my_table1
FOR EACH ROW
EXECUTE PROCEDURE sync_my_table1_to_my_table2();

-- 语句级触发器：处理批量插入
CREATE OR REPLACE TRIGGER sync_my_table1_statement_trigger
AFTER INSERT ON my_table1
REFERENCING NEW TABLE AS new_datas
FOR EACH STATEMENT
EXECUTE PROCEDURE sync_my_table1_to_my_table2();

可选方案：用视图替代触发器（更简洁） #

如果my_table2不需要独立存储a_column，可以创建视图直接关联my_table1，确保始终获取最新值：

CREATE VIEW my_table2_sync_view AS
SELECT 
    t2.*,
    -- 优先取my_table1的值，没有则保留my_table2原有的值（如果需要）
    COALESCE(t1.a_column, t2.a_column) AS a_column
FROM my_table2 t2
LEFT JOIN my_table1 t1 ON (t2.a, t2.b) = (t1.a, t1.b);

若需要修改视图中的my_table2字段，可创建可更新视图，将修改转发到基表。

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