如何在触发器中使用INNER JOIN实现关联字段更新？

方案1：数据库触发器（数据库自动处理） #

如果希望完全在数据库层面实现自动同步，无需修改应用代码，可以用触发器完成。不同数据库的触发器语法略有差异，以下是主流数据库的示例：

MySQL 示例

创建BEFORE INSERT触发器，在插入products记录前自动从stores表获取对应名称：

DELIMITER //
CREATE TRIGGER sync_product_store_name BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    -- 根据新插入的store_id查询对应门店名称
    SET NEW.store_name = (SELECT name FROM stores WHERE id = NEW.store_id);
    -- 可选：若store_id不存在，设置默认值或NULL
    -- IF NEW.store_name IS NULL THEN SET NEW.store_name = '未知门店'; END IF;
END //
DELIMITER ;

PostgreSQL 示例

PostgreSQL的触发器需结合函数使用：

-- 先创建触发函数
CREATE OR REPLACE FUNCTION sync_product_store_name()
RETURNS TRIGGER AS $$
BEGIN
    SELECT name INTO NEW.store_name FROM stores WHERE id = NEW.store_id;
    -- 可选处理不存在的情况
    -- IF NEW.store_name IS NULL THEN NEW.store_name = '未知门店'; END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER sync_product_store_name_trigger
BEFORE INSERT ON products
FOR EACH ROW EXECUTE FUNCTION sync_product_store_name();

方案2：应用层主动查询插入 #

如果不想依赖数据库触发器，也可以在应用代码中处理：插入产品数据前，先根据store_id从stores表查询对应的name，再将store_id和store_name一同插入products表。

比如Python（SQLAlchemy）示例：

# 查询门店名称
store = session.query(Stores).filter(Stores.id == new_product_store_id).first()
store_name = store.name if store else "未知门店"

# 插入产品数据
new_product = Products(store_id=new_product_store_id, store_name=store_name, ...)
session.add(new_product)
session.commit()

额外建议：是否需要冗余store_name字段？ #

store_name属于冗余字段，你随时可以通过JOIN查询获取最新门店名称：

SELECT p.*, s.name AS store_name
FROM products p
LEFT JOIN stores s ON p.store_id = s.id;

若业务无特殊性能需求，建议移除products表的store_name字段，避免后续stores表的name更新时，products表出现数据不一致（除非额外创建UPDATE触发器同步）。

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