Transaction表新增记录触发Expense写入的SQL Trigger设计咨询

以下为MySQL语法示例，其他数据库（SQL Server/PostgreSQL等）可对应调整语法，核心逻辑通用。

前置表结构假设 #

为避免字段歧义，先约定3张关联表的核心字段，你可根据实际业务表结构调整对应字段名：

• Service 表：存储服务明细，核心字段amount（DECIMAL类型，单条服务的金额）
• Expenses 表：存储费用明细，核心字段expense_amount（DECIMAL类型，费用金额）、expense_type（VARCHAR类型，费用类型标记）、create_time（DATETIME类型，费用创建时间）、related_transaction_id（INT类型，关联触发的事务ID，可选，用于幂等校验）
• Transaction 表：触发触发器的事务表，核心字段id（INT类型，主键ID）

触发器代码 #

-- 调试阶段可先删除已存在的同名触发器，避免创建冲突
DROP TRIGGER IF EXISTS trg_after_transaction_insert;

-- 修改分隔符，避免触发器内的分号和SQL默认分隔符冲突
DELIMITER //
CREATE TRIGGER trg_after_transaction_insert
-- 触发时机：Transaction表插入新记录之后触发
AFTER INSERT ON Transaction
-- 每插入1条Transaction记录就触发1次
FOR EACH ROW
BEGIN
    -- 声明变量存储Service表总金额的2%，精度可根据业务调整
    DECLARE service_2pct_fee DECIMAL(12,2);
    
    -- 计算Service表所有条目总金额的2%，IFNULL处理Service表为空的情况，避免返回NULL
    SELECT IFNULL(SUM(amount), 0) * 0.02 INTO service_2pct_fee FROM Service;
    
    -- 往Expenses表插入新条目，可按需补充其他字段
    INSERT INTO Expenses (expense_amount, expense_type, create_time, related_transaction_id)
    VALUES (service_2pct_fee, '服务总金额2%计提', NOW(), NEW.id);
END //
-- 改回默认分隔符
DELIMITER ;

入门开发注意事项 #

• 语法适配：不同数据库的触发器语法存在差异，比如SQL Server不需要修改DELIMITER，PostgreSQL需要先定义触发函数再绑定触发器，核心的计算+插入逻辑通用，可根据你使用的数据库调整语法即可
• 幂等性校验：上面的示例中新增了related_transaction_id关联Transaction的主键，可避免同一个事务重复触发生成多条费用，若业务不允许重复费用，建议加上该关联逻辑
• 事务一致性：AFTER INSERT类型的触发器默认和触发它的Transaction插入操作在同一个事务中，若触发器执行失败，Transaction的插入操作也会回滚，符合业务数据一致性要求
• 测试校验：触发器上线前可做3类测试：Service表为空时的插入测试、Service表有数据时的金额正确性校验、重复插入同一条Transaction的幂等性测试

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