如何编写触发器：逾期时计算sysdate与due_date差值并填充penalty表

问题背景 #

现有以下数据库表结构：

表结构 #

CREATE TABLE penalty(
    member_id number(11) NOT NULL,
    punishment_cost number(3),
    punishment_rate number(3),
    total_cost number(11)
);

CREATE TABLE member(
    member_id number(11) NOT NULL PRIMARY KEY,
    member_name varchar2(255) NOT NULL,
    member_surname varchar2(255) NOT NULL,
    gender varchar2(30) NOT NULL,
    birthdate DATE,
    phone varchar2(11),
    mail varchar2(255) NOT NULL,
    member_password varchar2(255) NOT NULL,
    address varchar2(255),
    status number(1),
    role_id number(10) NOT NULL
);

CREATE TABLE item(
    item_id varchar2(255) NOT NULL PRIMARY KEY,
    member_id number(11),
    publish_date DATE,
    is_occupied NUMBER(1) NOT NULL,
    publisher_id number(11) NOT NULL,
    category_name varchar2(255) NOT NULL,
    loan_id number(10)
);

CREATE TABLE current_loan(
    loan_id number(10) NOT NULL PRIMARY KEY,
    loan_date DATE,
    due_date DATE
);

需求：当借阅物品的due_date（到期日期）超过系统日期sysdate时，自动计算逾期天数，将对应member_id、按公式total_cost = penalty_rate * days计算的总费用插入penalty表。例如会员借阅书籍的loan_date为今日，due_date为明日，逾期2天后需向penalty表插入新行。

解决方案 #

Oracle触发器无法主动监听日期变化，这里提供两种适配业务场景的实现思路：

思路1：定时任务+存储过程（推荐） #

这是最贴合“日期到期自动触发”需求的方案，通过定时任务每日执行计算逻辑：

1. 创建计算逾期罚金的存储过程

CREATE OR REPLACE PROCEDURE calculate_overdue_penalties AS
    CURSOR overdue_loans IS
        SELECT 
            i.member_id,
            5 AS penalty_rate, -- 可替换为从配置表读取的动态费率
            FLOOR(SYSDATE - cl.due_date) AS overdue_days
        FROM current_loan cl
        JOIN item i ON cl.loan_id = i.loan_id
        WHERE cl.due_date < SYSDATE
        -- 避免重复插入同一逾期记录，可根据业务调整判断条件
        AND NOT EXISTS (
            SELECT 1 FROM penalty p 
            WHERE p.member_id = i.member_id 
            AND p.total_cost = 5 * FLOOR(SYSDATE - cl.due_date)
        );
BEGIN
    FOR loan IN overdue_loans LOOP
        INSERT INTO penalty(member_id, punishment_rate, total_cost)
        VALUES(loan.member_id, loan.penalty_rate, loan.penalty_rate * loan.overdue_days);
    END LOOP;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

2. 创建每日执行的定时任务

BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'CALCULATE_OVERDUE_PENALTIES_JOB',
        job_type => 'STORED_PROCEDURE',
        job_action => 'calculate_overdue_penalties',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0;', -- 每天凌晨执行
        enabled => TRUE,
        comments => '每日计算逾期用户罚金并写入penalty表'
    );
END;
/

思路2：操作触发式触发器 #

如果不需要定时自动执行，可在查询/更新current_loan时触发计算逻辑：

CREATE OR REPLACE TRIGGER check_overdue_on_access
AFTER SELECT OR UPDATE OF due_date ON current_loan
FOR EACH ROW
DECLARE
    v_member_id member.member_id%TYPE;
    v_overdue_days NUMBER;
    v_penalty_rate NUMBER := 5; -- 可替换为动态获取逻辑
BEGIN
    -- 获取对应会员ID
    SELECT member_id INTO v_member_id
    FROM item
    WHERE loan_id = :NEW.loan_id;
    
    -- 计算并插入罚金
    IF :NEW.due_date < SYSDATE THEN
        v_overdue_days := FLOOR(SYSDATE - :NEW.due_date);
        
        IF NOT EXISTS (
            SELECT 1 FROM penalty p
            WHERE p.member_id = v_member_id
            AND p.total_cost = v_penalty_rate * v_overdue_days
        ) THEN
            INSERT INTO penalty(member_id, punishment_rate, total_cost)
            VALUES(v_member_id, v_penalty_rate, v_penalty_rate * v_overdue_days);
            COMMIT;
        END IF;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL; -- 处理无对应item的情况
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

注意事项 #

• 若penalty_rate不是固定值，建议新增penalty_config配置表存储不同分类的费率，在逻辑中关联查询获取。
• 重复记录的判断条件需结合实际业务调整，避免同一逾期周期多次插入。
• 定时任务的执行频率可按需修改（如每小时执行一次）。

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