如何编写SQL触发器函数在transactions表插入时更新门店均值

现有代码的核心问题 #

之前的触发器仅能更新单店数据，通常由三个问题导致：

• 触发器为行级触发，逻辑中仅处理当前插入单行对应的单个store_id，没有覆盖批量插入多门店数据的场景
• 均值计算逻辑没有对本次插入涉及的所有门店做统一重算，仅更新了单店数值
• 初始化store_averages的语句存在冗余，且未给均值字段设置明确别名、未加主键约束，容易导致更新异常或重复数据

表结构初始化修正 #

原建store_averages的语句分两次插入两家门店数据属于冗余操作，可直接简化，同时补充约束避免脏数据：

-- 清理之前错误创建的表
DROP TABLE IF EXISTS store_averages;

-- 一次性初始化所有门店的均值数据，指定明确字段名
CREATE TABLE store_averages AS
SELECT
  store_id,
  AVG(amount) AS avg_amount
FROM transactions
GROUP BY store_id;

-- 给门店ID加主键，确保每个门店仅存在一条均值记录
ALTER TABLE store_averages ADD PRIMARY KEY (store_id);

触发器编写与绑定 #

根据你使用的数据库类型选择对应实现即可，两种实现都支持同时更新两家门店的均值：

PostgreSQL / SQL Server 版本（支持语句级触发器，性能更优） #

首先创建触发器函数：

-- PostgreSQL 版本函数
CREATE OR REPLACE FUNCTION fn_refresh_store_avg()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO store_averages (store_id, avg_amount)
  SELECT
    store_id,
    AVG(amount)
  FROM transactions
  -- 仅重算本次插入涉及到的门店，无需全表扫描
  WHERE store_id IN (SELECT DISTINCT store_id FROM inserted)
  GROUP BY store_id
  ON CONFLICT (store_id)
  DO UPDATE SET avg_amount = EXCLUDED.avg_amount;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

SQL Server 不需要单独创建函数，直接在触发器内写上述INSERT逻辑即可，系统自带inserted表存储本次插入的所有记录。

绑定触发器到交易表的插入事件：

-- PostgreSQL 绑定语句级触发器，批量插入仅触发一次
CREATE TRIGGER trg_trans_after_insert
AFTER INSERT ON transactions
FOR EACH STATEMENT
EXECUTE FUNCTION fn_refresh_store_avg();

MySQL 版本适配 #

MySQL 仅支持行级触发器，考虑到你仅需要追踪2家门店，数据量极小，直接用全量重算逻辑即可，逻辑简单不会出错：

DELIMITER //
CREATE TRIGGER trg_trans_after_insert
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
  -- 重算1号门店均值
  REPLACE INTO store_averages(store_id, avg_amount)
  SELECT store_id, AVG(amount) FROM transactions WHERE store_id = 1;
  -- 重算2号门店均值
  REPLACE INTO store_averages(store_id, avg_amount)
  SELECT store_id, AVG(amount) FROM transactions WHERE store_id = 2;
END //
DELIMITER ;

功能验证 #

插入跨两家门店的测试数据验证效果：

INSERT INTO transactions(payment_id, payment_date, store_id, amount)
VALUES
(1, '2024-05-01 09:30:00', 1, 25.0),
(2, '2024-05-01 09:35:00', 1, 35.0),
(3, '2024-05-01 09:40:00', 2, 70.0),
(4, '2024-05-01 09:45:00', 2, 90.0);

-- 查询结果会显示1号店均值30、2号店均值80，两家数据同步更新
SELECT * FROM store_averages;

如果后续需要支持交易记录删除、金额修改的场景，给UPDATE、DELETE事件绑定同一个触发器逻辑即可。

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