如何编写触发器实现Records表变动时同步更新Count表的计数字段

以下是基于MySQL语法的实现方案，其他关系型数据库（如PostgreSQL、SQL Server）语法略有差异但核心逻辑一致。

1. 表结构说明 #

• Count表：存储各记录类型的总计数
  • countRecords：对应类型的记录总条数
  • typeRecord：记录类型，和Records表的recordType字段关联
• Records表：存储单条记录明细
  • ID：记录唯一主键
  • recordType：当前记录的类型

2. 新增记录触发计数加1的触发器 #

当Records表插入新数据后，自动更新Count表对应类型的计数：

DELIMITER //
CREATE TRIGGER trg_records_after_insert
AFTER INSERT ON Records
FOR EACH ROW
BEGIN
  -- 更新对应类型的计数，加1
  UPDATE Count 
  SET countRecords = countRecords + 1 
  WHERE typeRecord = NEW.recordType;
  
  -- 可选逻辑：如果Count表不存在该类型的记录，自动插入初始化记录
  -- 如果你已经提前在Count表中配置了所有可能的recordType，可以删除这部分逻辑
  -- IF ROW_COUNT() = 0 THEN
  --   INSERT INTO Count (countRecords, typeRecord) VALUES (1, NEW.recordType);
  -- END IF;
END //
DELIMITER ;

注：如果开启自动初始化逻辑，需要保证typeRecord字段不会出现非法值

3. 删除记录触发计数减1的触发器 #

当Records表删除数据后，自动更新Count表对应类型的计数：

DELIMITER //
CREATE TRIGGER trg_records_after_delete
AFTER DELETE ON Records
FOR EACH ROW
BEGIN
  UPDATE Count 
  SET countRecords = countRecords - 1 
  WHERE typeRecord = OLD.recordType
  AND countRecords > 0; -- 避免计数出现负数
END //
DELIMITER ;

注意事项 #

• 如果存在更新Records表recordType字段的业务场景，还需要额外添加AFTER UPDATE触发器，实现旧类型计数减1、新类型计数加1的逻辑
• 触发器逻辑和业务操作默认在同一事务内，一方失败会整体回滚，可保证数据一致性
• 高并发场景下需要注意行锁冲突问题，避免计数更新出现异常

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