如何创建触发器以在基表更新时调用存储过程插入处理后数据

以下是两种最常用的触发实现方式，适用场景、代码示例和最佳实践如下：

方案1：插入后触发器（实时同步） #

适用场景 #

• 汇总表数据实时性要求高，基表插入后需要立即更新汇总结果
• 基表单条写入频率低，不会因触发逻辑带来明显性能损耗

核心逻辑 #

在基表上绑定AFTER INSERT触发器，每次基表有新数据写入时，自动调用存储过程处理新增数据。

注意：你当前的存储过程是全量加工逻辑，直接调用会重复插入汇总数据，需要先改造为增量处理。

最简代码示例（MySQL语法，其他数据库语法差异极小） #

-- 1. 改造原存储过程为单条增量处理，入参为基表新插入行的主键ID
CREATE PROCEDURE x(IN new_base_id INT)
AS 
BEGIN
INSERT INTO summary_table(a, b, c, d, e, f, g, h)
-- 查询条件限定仅处理新增的单条数据
SELECT a,b,c,d,e,f,g,h FROM 基表 WHERE id = new_base_id;
END x;

-- 2. 创建基表插入触发器
DELIMITER //
CREATE TRIGGER trg_base_insert_sync_summary
AFTER INSERT ON 基表
FOR EACH ROW
BEGIN
  -- 传入新插入行的主键触发增量同步
  CALL x(NEW.id);
END //
DELIMITER ;

最佳实践 #

• 触发器仅做存储过程调用，所有加工逻辑全部收敛到存储过程内，降低维护成本
• 汇总表必须加唯一业务约束，配合INSERT IGNORE或ON DUPLICATE KEY UPDATE兜底避免重复数据
• 高并发写入场景禁止使用触发器，会拉长单次写入耗时，极易引发锁等待和写入超时

方案2：定时任务调度（异步批量同步） #

适用场景 #

• 基表写入频率高，对汇总表数据实时性要求低（可接受分钟/小时/天级延迟）
• 优先保障基表写入性能，不能接受触发器带来的额外开销

核心逻辑 #

按固定周期调用存储过程，每次仅处理上一次同步完成后新增的批量数据，通过独立的位点表记录同步进度，避免重复处理。

最简代码示例 #

-- 1. 新建同步位点表，记录每次同步的最大基表ID
CREATE TABLE sync_position (
  table_name VARCHAR(100) PRIMARY KEY COMMENT '关联的基表名',
  last_sync_max_id BIGINT NOT NULL DEFAULT 0 COMMENT '上次同步完成的最大主键ID'
);
-- 初始化基表同步位点
INSERT INTO sync_position(table_name, last_sync_max_id) VALUES ('基表', 0);

-- 2. 改造原存储过程为批量增量处理
CREATE PROCEDURE x()
AS 
BEGIN
  -- 开启事务保证数据和位点的一致性
  START TRANSACTION;
  -- 获取本次同步的最大ID位点
  SET @current_max_id = (SELECT IFNULL(MAX(id),0) FROM 基表);
  -- 获取上次同步的结束位点
  SET @last_sync_id = (SELECT last_sync_max_id FROM sync_position WHERE table_name = '基表' FOR UPDATE);
  
  -- 仅处理上一次同步后新增的批量数据
  INSERT INTO summary_table(a, b, c, d, e, f, g, h)
  SELECT a,b,c,d,e,f,g,h FROM 基表 WHERE id > @last_sync_id AND id <= @current_max_id;
  
  -- 更新同步位点
  UPDATE sync_position SET last_sync_max_id = @current_max_id WHERE table_name = '基表';
  COMMIT;
END x;

-- 3. MySQL内置事件调度器配置定时任务（其他数据库可使用对应调度工具：SQL Server Agent、PostgreSQL pg_cron、操作系统crontab等）
-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
-- 创建每10分钟执行一次的同步任务
CREATE EVENT evt_sync_summary_table
ON SCHEDULE EVERY 10 MINUTE
STARTS CURRENT_TIMESTAMP
DO CALL x();

最佳实践 #

• 同步逻辑必须加事务包裹，避免同步中途失败导致位点更新但数据未写入的不一致问题
• 定期校验汇总表和基表的聚合结果一致性，补正漏同步的异常数据
• 同步频率根据业务容忍的延迟和数据量灵活调整，避免频繁调度带来的无效资源消耗
• 写入性能远高于触发器方案，适合大数据量高并发的生产场景

选型建议 #

• 实时性要求高、基表写入量小选触发器方案
• 可接受延迟、基表写入量大选定时任务方案

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