Oracle触发器执行报ORA-01847错误无法插入数据如何修复

错误根源分析 #

• 时间计算逻辑错误：两个TIMESTAMP类型相减得到的是INTERVAL DAY TO SECOND类型，原代码直接使用TO_DATE()对该结果做转换属于类型不匹配，这是触发ORA-01847的直接原因。
• 业务逻辑错误：原触发器是全表扫描所有师傅的日工作时长，只要全表存在任意一条超时记录就阻止所有插入/更新，完全不符合「仅限制当前操作的师傅当日时长不超480分钟」的需求。
• 语法逻辑错误：行级触发器中不允许直接执行ROLLBACK语句，需要通过抛出自定义异常来终止DML操作。
• 潜在变异表问题：行级触发器中直接查询触发触发器的表T_SCHEDULE，会触发ORA-04091变异表错误，本次是第一条插入后表有数据才触发报错，后续操作会持续异常。

修复后的触发器代码 #

CREATE OR REPLACE TRIGGER HOURS_A_DAY
BEFORE INSERT OR UPDATE ON T_SCHEDULE
FOR EACH ROW
DECLARE
    v_total_min NUMBER := 0;
    v_current_min NUMBER;
BEGIN
    -- 前置校验：结束时间不能早于开始时间
    IF :NEW.job_stop < :NEW.job_start THEN
        RAISE_APPLICATION_ERROR(-20001, '任务结束时间不能早于开始时间');
    END IF;

    -- 计算当前新插入/更新行的工作时长（单位：分钟）
    v_current_min := EXTRACT(HOUR FROM (:NEW.job_stop - :NEW.job_start)) * 60 
                   + EXTRACT(MINUTE FROM (:NEW.job_stop - :NEW.job_start));

    -- 查询当前师傅当天已有的累计工作时长（更新操作时排除当前行的旧记录，避免重复计算）
    SELECT NVL(SUM(EXTRACT(HOUR FROM (job_stop - job_start)) * 60 + EXTRACT(MINUTE FROM (job_stop - job_start))), 0)
    INTO v_total_min
    FROM T_SCHEDULE
    WHERE master_id = :NEW.master_id
      AND TRUNC(job_start) = TRUNC(:NEW.job_start)
      AND sched_id != :NEW.sched_id;

    -- 累计时长超过480分钟则抛异常终止操作
    IF v_total_min + v_current_min > 480 THEN
        RAISE_APPLICATION_ERROR(-20002, '师傅当日累计工作时长不可超过480分钟');
    END IF;
END;
/

补充说明 #

你提供的测试数据中sched_id=4006的行存在逻辑错误：任务开始时间为2021-10-03，结束时间为2021-10-02，会被触发器的前置校验拦截，需要修正时间顺序后再插入。如果业务场景存在跨天的工作任务，需要调整触发器的日期统计逻辑适配跨天场景。

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