如何创建触发器实现关联表更新时同步更新主键表modified_at字段？

问题描述 #

我有一个含主键的product表，结构如下：

product
-id
-modified_at

另有以product_id作为外键的inventory和options表，结构如下：

inventory
-id
-product_id

options
-id
-product_id

需求：每当inventory或options表发生更新（包括插入、更新、删除操作）时，自动将product表中对应记录的modified_at字段更新为当前时间，预期执行语句为：update product set modified_at=NOW() where id=@product_id

解决方案 #

完全可以通过触发器实现这个需求。你需要为inventory和options分别创建触发器，监听它们的插入、更新、删除操作，在操作完成后同步更新对应product记录的modified_at字段。以下是MySQL环境下的具体实现：

1. inventory表触发器 #

插入后触发

DELIMITER //
CREATE TRIGGER trg_inventory_insert_after
AFTER INSERT ON inventory
FOR EACH ROW
BEGIN
    UPDATE product 
    SET modified_at = NOW() 
    WHERE id = NEW.product_id;
END //
DELIMITER ;

更新后触发

DELIMITER //
CREATE TRIGGER trg_inventory_update_after
AFTER UPDATE ON inventory
FOR EACH ROW
BEGIN
    -- 若product_id变更，需同时更新新旧两个关联产品的时间戳
    IF OLD.product_id != NEW.product_id THEN
        UPDATE product 
        SET modified_at = NOW() 
        WHERE id = OLD.product_id;
    END IF;
    UPDATE product 
    SET modified_at = NOW() 
    WHERE id = NEW.product_id;
END //
DELIMITER ;

删除后触发

DELIMITER //
CREATE TRIGGER trg_inventory_delete_after
AFTER DELETE ON inventory
FOR EACH ROW
BEGIN
    UPDATE product 
    SET modified_at = NOW() 
    WHERE id = OLD.product_id;
END //
DELIMITER ;

2. options表触发器 #

逻辑与inventory表一致，仅需替换表名：

插入后触发

DELIMITER //
CREATE TRIGGER trg_options_insert_after
AFTER INSERT ON options
FOR EACH ROW
BEGIN
    UPDATE product 
    SET modified_at = NOW() 
    WHERE id = NEW.product_id;
END //
DELIMITER ;

更新后触发

DELIMITER //
CREATE TRIGGER trg_options_update_after
AFTER UPDATE ON options
FOR EACH ROW
BEGIN
    IF OLD.product_id != NEW.product_id THEN
        UPDATE product 
        SET modified_at = NOW() 
        WHERE id = OLD.product_id;
    END IF;
    UPDATE product 
    SET modified_at = NOW() 
    WHERE id = NEW.product_id;
END //
DELIMITER ;

删除后触发

DELIMITER //
CREATE TRIGGER trg_options_delete_after
AFTER DELETE ON options
FOR EACH ROW
BEGIN
    UPDATE product 
    SET modified_at = NOW() 
    WHERE id = OLD.product_id;
END //
DELIMITER ;

关键说明 #

• 使用AFTER触发时机，确保关联表的写操作成功执行后再更新主表，避免数据不一致
• 更新触发器中处理了product_id变更的场景，防止遗漏旧关联产品的时间戳更新
• 若使用PostgreSQL等其他数据库，语法会有差异，但核心逻辑始终是：监听关联表的写操作，同步更新主表的时间字段

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