怎样使用PostgreSQL SQL触发器实现多表数据的双向同步-SQL-PHP中文网

双向同步需设update_data_source字段防循环，default 0 not null；after insert/update/delete须分写；跨库优先用postgres_fdw；聚合或高频表应避免触发器。

怎样使用postgresql sql触发器实现多表数据的双向同步

双向同步必须绕开 UPDATE_DATA_SOURCE 字段防循环

双向同步（A 表改 → B 表同步，B 表改 → A 表同步）天然会触发嵌套调用，不加拦截就会无限递归。最常用且有效的拦截手段是引入一个标记字段，比如 UPDATE_DATA_SOURCE，约定：只有值为 0（表示“业务写入”）时才触发同步；触发器自己执行的写入，必须显式设为 1 或其他非零值。

这个字段不能靠应用层“记得填”，必须设为列默认值 + NOT NULL 约束：

ALTER TABLE a_table ADD COLUMN UPDATE_DATA_SOURCE SMALLINT DEFAULT 0 NOT NULL;

否则任意一条绕过应用层的直连 SQL（如 DBA 手动 INSERT、ETL 工具导入）都会让同步逻辑失效。

AFTER INSERT/UPDATE/DELETE 必须分开写，别塞进一个触发器

混写一个触发器处理三种 DML 类型，会导致逻辑混乱、调试困难、运行时报错难定位。比如在 INSERT 场景下读 DELETED 表，PostgreSQL 里根本不存在这个表，直接报错；SQL Server 里 DELETED 为空，但代码没判空就引用，也会崩。

正确做法是按操作类型拆开：

AFTER INSERT：只读 NEW，同步新增记录到对端表
AFTER UPDATE：对比 NEW 和 OLD，只同步真正变化的字段（例如只改了 name，就别碰 email）
AFTER DELETE：只读 OLD，清理对端对应行（注意外键级联是否已做这事，重复删可能报错）

跨库同步优先用 postgres_fdw，别硬上 dblink

虽然 dblink 能实现跨库写入，但它要求触发器函数里手动拼接连接字符串、管理连接生命周期，容易泄露连接、超时卡死事务。而 postgres_fdw 是 PostgreSQL 官方推荐的跨库访问方式，把远端表映射成本地 FOREIGN TABLE 后，触发器里就能像操作本地表一样 INSERT/UPDATE/DELETE，语义清晰、错误明确、事务自动传播。

关键步骤：

源库执行：CREATE EXTENSION IF NOT EXISTS postgres_fdw;
创建远程服务：CREATE SERVER remote_b SERVER postgres_fdw OPTIONS (host '192.168.1.100', port '5432', dbname 'b_db');
建用户映射：CREATE USER MAPPING FOR CURRENT_USER SERVER remote_b OPTIONS (user 'sync_user', password 'xxx');
建外部表：CREATE FOREIGN TABLE b_table (...) SERVER remote_b OPTIONS (table_name 'b_table');

之后触发器函数里直接写 INSERT INTO b_table SELECT ... FROM NEW; 即可。