如何编写触发器为Elec_prod表按日期自动填充Id_tariff？

核心需求 #

向Elec_prod表插入记录时，以SYSDATE作为Date_measurement的值，自动从e_tariffs表匹配并填充对应的Id_tariff字段。

推荐实现方案：行级BEFORE INSERT触发器 #

不需要用复合触发器全表加载循环，直接通过SQL查询匹配对应 tariff，效率更高、代码更简洁：

CREATE OR REPLACE TRIGGER trg_elec_prod_fill_tariff
BEFORE INSERT ON Elec_prod
FOR EACH ROW
WHEN (NEW.Date_measurement IS NULL OR NEW.Date_measurement = SYSDATE)
BEGIN
    -- 根据SYSDATE匹配对应的Id_tariff，假设e_tariffs按时间区间生效（Date_start到Date_end）
    SELECT Id_tariff
    INTO :NEW.Id_tariff
    FROM e_tariffs
    WHERE SYSDATE BETWEEN Date_start AND NVL(Date_end, SYSDATE + INTERVAL '1' DAY)
    -- 如果是取最新生效的 tariff（无结束时间时取最近的起始时间）
    -- ORDER BY Date_start DESC
    -- FETCH FIRST 1 ROW ONLY;
    ;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 处理无匹配 tariff 的情况，比如赋值默认值或抛出异常
        :NEW.Id_tariff := 0; -- 示例默认值
    WHEN TOO_MANY_ROWS THEN
        -- 处理多个匹配 tariff 的情况，比如取最新的或抛出异常
        SELECT Id_tariff
        INTO :NEW.Id_tariff
        FROM e_tariffs
        WHERE SYSDATE BETWEEN Date_start AND NVL(Date_end, SYSDATE + INTERVAL '1' DAY)
        ORDER BY Date_start DESC
        FETCH FIRST 1 ROW ONLY;
END;
/

方案说明 #

1. 触发器时机：BEFORE INSERT确保在记录插入前完成Id_tariff的赋值，无需后续更新。
2. 匹配逻辑：根据e_tariffs的时间规则调整查询条件——如果是区间生效，用BETWEEN；如果是按最新生效时间覆盖，用ORDER BY Date_start DESC FETCH FIRST 1 ROW ONLY。
3. 异常处理：避免因无匹配或多匹配导致插入失败，根据业务需求自定义处理逻辑。

关于你的复合触发器思路 #

复合触发器全表加载e_tariffs再循环比较的方法可行，但存在明显弊端：

• 当e_tariffs数据量较大时，全表加载会占用额外内存，降低插入性能。
• 循环比较的逻辑复杂度高于直接SQL查询，维护成本更高。

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