创建Enrollment插入新行时自动扣减Journey表剩余座位的SQL触发器
行程报名自动扣减座位触发器实现方案
业务表定义
Journey表:存储行程基础信息,核心字段包括Journey_ID(主键)、Seats(剩余可用座位数)、Price,每个行程预设固定座位数上限Enrollment表:存储用户行程报名记录,核心字段包括Enrollment_ID(主键)、Journey_ID(外键,关联Journey表的Journey_ID主键)
原始参考代码的问题
最初编写的触发器无法正常运行、满足业务规则,存在以下缺陷:
- 命名不匹配:更新操作的目标表写为
Journeys(复数),与实际定义的Journey表名不一致;关联条件使用的Journeys.Id、inserted.Journeys_ID与实际字段名Journey_ID不匹配,运行时会触发对象不存在的报错- 逻辑漏洞:代码仅对每条插入记录执行座位减1操作,未做剩余座位校验,会出现Seats被扣减为负数的超卖问题;同时未对批量插入同行程报名的场景做统一计数优化,存在性能隐患
生产可用的修正版触发器代码
CREATE TRIGGER trg_Enrollment_DecreaseJourneySeats ON Enrollment AFTER INSERT AS BEGIN -- 关闭额外行数提示,避免干扰业务接口返回 SET NOCOUNT ON; BEGIN TRANSACTION -- 按行程聚合统计本次新增报名人数,批量计算扣减额度 UPDATE j SET Seats = j.Seats - stats.enroll_count FROM Journey j INNER JOIN ( SELECT Journey_ID, COUNT(1) AS enroll_count FROM inserted GROUP BY Journey_ID ) stats ON j.Journey_ID = stats.Journey_ID -- 超卖校验:扣减后出现座位负数则回滚事务并报错 IF EXISTS (SELECT 1 FROM Journey WHERE Seats < 0) BEGIN ROLLBACK TRANSACTION; THROW 50001, '对应行程剩余座位不足,无法完成报名', 1; END COMMIT TRANSACTION END GO
核心实现特性
- 适配批量插入:通过对
inserted系统临时表按行程ID聚合统计报名量,避免逐行更新的性能损耗,同时保证计数准确 - 事务一致性保障:报名记录写入、座位扣减操作绑定在同一事务内,任意环节失败都会全量回滚,不会出现两边数据不一致的问题
- 内置超卖拦截:扣减完成后即时校验剩余座位阈值,不足时直接抛出明确的业务错误,避免无效报名占用资源
内容的提问来源于stack exchange,提问作者Mario Thoumy
相关产品推荐
相关产品推荐

