PostgreSQL触发器实现：更新providers时同步更新users表字段

解决方案概述 #

通过创建行级AFTER触发器，在providers表的first_name或last_name字段更新后，自动同步更新users表中对应user_id记录的同名字段，确保数据一致性，同时不影响providers表的生成列逻辑。

1. 创建触发器函数 #

首先编写PL/pgSQL函数处理同步逻辑：

CREATE OR REPLACE FUNCTION sync_provider_name_to_user()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅当first_name或last_name发生变化时执行同步
    IF NEW.first_name IS DISTINCT FROM OLD.first_name OR NEW.last_name IS DISTINCT FROM OLD.last_name THEN
        -- 更新users表中对应user_id的姓名字段
        UPDATE users
        SET first_name = NEW.first_name,
            last_name = NEW.last_name
        WHERE user_id = NEW.user_id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 创建触发器 #

将函数绑定到providers表的更新事件，仅在指定字段变更时触发：

CREATE TRIGGER trigger_sync_provider_name
AFTER UPDATE OF first_name, last_name ON providers
FOR EACH ROW
EXECUTE FUNCTION sync_provider_name_to_user();

关键细节说明 #

• 触发时机选择：使用AFTER UPDATE而非BEFORE UPDATE，确保providers表的生成列已基于更新后的姓名字段完成计算，再同步users表，避免中间状态导致的数据不一致。
• 字段变化判断：IS DISTINCT FROM能正确处理NULL值场景（如从非NULL变为NULL），比直接使用!=更严谨，不会因为NULL值判断失效而跳过同步。
• 空值防护：若providers的user_id为NULL，UPDATE语句不会执行任何操作。如果业务要求user_id必须非空，可以在函数中添加异常抛出逻辑：

  IF NEW.user_id IS NULL THEN
      RAISE EXCEPTION 'user_id cannot be NULL when updating provider name';
  END IF;
  

• 性能优化：通过UPDATE OF first_name, last_name限定触发条件，只有当指定字段变更时才执行触发器逻辑，避免无意义的资源消耗。
• 事务一致性：触发器与原更新操作处于同一事务中，若users表更新失败，整个事务会回滚，保证两张表的数据始终一致。

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