如何编写SQL触发器实现月度支付总额统计与周度配送总量计算

你参考的基础触发器语法完全可以满足月度支付金额统计、周度配送总量统计的需求，以下是具体实现方案：

前置准备 #

你需要先创建对应统计结果的存储表，用来存储触发器计算得到的统计值。

1. 月度利润总额统计 #

首先创建月度利润存储表：

CREATE TABLE MONTHLY_PROFIT (
    STAT_MONTH VARCHAR2(7) PRIMARY KEY, -- 统计月份，格式如'2024-05'
    TOTAL_AMOUNT NUMERIC(12) NOT NULL -- 月度总支付金额
);

对应触发器（遵循你提供的基础语法结构编写，Oracle语法示例）：

CREATE TRIGGER trg_calc_monthly_profit
AFTER INSERT ON PAYMENT
FOR EACH ROW
WHEN (NEW.PAY_CONF = 1) -- 仅统计支付确认成功的记录
BEGIN
    -- 先尝试更新当月已有统计记录
    UPDATE MONTHLY_PROFIT 
    SET TOTAL_AMOUNT = TOTAL_AMOUNT + NEW.PRICE
    WHERE STAT_MONTH = TO_CHAR(NEW.PAY_DATE, 'YYYY-MM');
    
    -- 如果当月没有统计记录则插入新记录
    IF SQL%ROWCOUNT = 0 THEN
        INSERT INTO MONTHLY_PROFIT(STAT_MONTH, TOTAL_AMOUNT)
        VALUES (TO_CHAR(NEW.PAY_DATE, 'YYYY-MM'), NEW.PRICE);
    END IF;
END;

2. 周度配送总量统计 #

首先创建周度配送存储表：

CREATE TABLE WEEKLY_DELIVERY (
    STAT_WEEK VARCHAR2(7) PRIMARY KEY, -- 统计周，格式如'2024-W20'
    TOTAL_DELIVERY NUMERIC(10) NOT NULL -- 周度完成配送总量
);

对应触发器：

CREATE TRIGGER trg_calc_weekly_delivery
AFTER INSERT ON DELIVERY
FOR EACH ROW
WHEN (NEW.STATUS = '完成') -- 仅统计配送完成的记录
BEGIN
    -- 先尝试更新当周已有统计记录
    UPDATE WEEKLY_DELIVERY
    SET TOTAL_DELIVERY = TOTAL_DELIVERY + 1
    WHERE STAT_WEEK = TO_CHAR(NEW.DEL_DATE, 'YYYY-"W"IW');
    
    -- 如果当周没有统计记录则插入新记录
    IF SQL%ROWCOUNT = 0 THEN
        INSERT INTO WEEKLY_DELIVERY(STAT_WEEK, TOTAL_DELIVERY)
        VALUES (TO_CHAR(NEW.DEL_DATE, 'YYYY-"W"IW'), 1);
    END IF;
END;

注意事项 #

• 若业务场景中存在修改、删除PAYMENT或DELIVERY表记录的需求，需要额外补充AFTER UPDATE、AFTER DELETE类型的触发器，保证统计数据和业务数据一致性
• 上述示例使用Oracle语法编写，如果你使用其他数据库（如MySQL、SQL Server），仅需要调整时间格式化函数、触发器语法的细节部分，核心逻辑和你参考的基础触发器框架完全一致
• 你提供的DELIVERY表建表语句最后一个外键约束末尾多了多余的逗号，删除后即可正常执行

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