如何实现SQL表的数据镜像同步_配置AFTER INSERT/UPDATE双向触发器-SQL-PHP中文网

after触发器无法实现真正可靠的双向同步，因其在语句执行后触发，若a表触发器修改b表、b表触发器又反向修改a表，将引发无限递归（a→b→a→b…），导致sql server报“maximum trigger nesting level exceeded”、mysql报error 1442、postgresql触发深度限制或卡死；必须拆分为两个独立触发器并严格嵌入防循环机制（如trigger_nestlevel()或pg_trigger_depth()判断），否则必然死循环或数据错乱。

如何实现sql表的数据镜像同步_配置after insert/update双向触发器

不能靠单个 AFTER 触发器实现真正可靠的双向同步，必须拆成两个独立触发器 + 严格防循环机制，否则必然出现死循环或数据错乱。

为什么 AFTER INSERT/UPDATE 触发器不能直接“双向”写

AFTER 触发器在语句执行完成后才触发，如果 A 表的触发器去改 B 表，而 B 表也有一个同样逻辑的触发器去改 A 表，一次 INSERT 就会引发无限递归：A → B → A → B … 直到栈溢出或超时。SQL Server 会报 Maximum trigger nesting level exceeded，MySQL 报 ERROR 1442（虽非同一错误码，但本质相同），PostgreSQL 则可能卡死或触发 pg_trigger_depth() 限制。

常见误操作包括：

在 A 表触发器里无条件执行 UPDATE B SET ...，又在 B 表触发器里对 A 做同样操作
用 INSERT INTO B SELECT * FROM inserted 同步新增，但没过滤掉“本就是由 B 表变更引发的这次插入”
把同步逻辑写在 AFTER 触发器里，却没检查 TRIGGER_NESTLEVEL()（SQL Server）或 pg_trigger_depth()（PG）

SQL Server：用 TRIGGER_NESTLEVEL() + 链接服务器做单向同步更稳

SQL Server 不支持跨库触发器自动感知来源，所以“双向”必须靠应用层或中间件协调；若硬要用触发器，只能退一步做“主从式单向同步”，再配另一套反向链路（如 CDC 或轮询）。但如果你坚持用触发器推数据，务必加嵌套层级控制：

CREATE TRIGGER tr_sync_A_to_B ON A AFTER INSERT, UPDATE, DELETE AS
BEGIN
  SET NOCOUNT ON;
  IF TRIGGER_NESTLEVEL() > 1 RETURN; -- 关键！跳过被B表触发器间接调用的情况
<p>IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
INSERT INTO LinkServer.DB.dbo.B (id, name, updated_at)
SELECT id, name, GETDATE() FROM inserted;</p><p>IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
UPDATE b SET b.name = i.name, b.updated_at = GETDATE()
FROM LinkServer.DB.dbo.B b
INNER JOIN inserted i ON b.id = i.id;</p><p>IF EXISTS (SELECT 1 FROM deleted) AND NOT EXISTS (SELECT 1 FROM inserted)
DELETE FROM LinkServer.DB.dbo.B WHERE id IN (SELECT id FROM deleted);
END</p>

注意：LinkServer.DB.dbo.B 必须提前建好链接服务器，且账号有写权限；TRIGGER_NESTLEVEL() 是 SQL Server 唯一能可靠识别“是不是我主动发起的”手段，别信 @@NESTLEVEL ——它不区分触发器层级。

PostgreSQL：用 pg_trigger_depth() + 条件更新避免循环

PG 允许 AFTER 触发器更新原表，也允许跨表更新，但双向同步仍需手动破环。推荐做法是：只在 A 表触发器中同步 B 表，B 表触发器只同步 A 表，并各自加深度判断：

CREATE OR REPLACE FUNCTION sync_a_to_b() RETURNS TRIGGER AS $$
BEGIN
  IF pg_trigger_depth() > 1 THEN RETURN NULL; END IF;
  IF TG_OP = 'INSERT' THEN
    INSERT INTO b(id, name, updated_at) VALUES (NEW.id, NEW.name, NOW())
      ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, updated_at = NOW();
  ELSIF TG_OP = 'UPDATE' THEN
    UPDATE b SET name = NEW.name, updated_at = NOW() WHERE id = NEW.id;
  ELSIF TG_OP = 'DELETE' THEN
    DELETE FROM b WHERE id = OLD.id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

关键点：