Oracle创建触发器记录INSERT/UPDATE操作新旧值方案

• 触发器直接创建在基表faktura上，不需要在视图上创建触发器。所有通过可更新视图view_faktura执行的增改操作最终都会落到基表，会正常触发基表行级触发器，覆盖所有操作路径。
• PLS-00103编译错误90%以上是PL/SQL块结构不完整（缺少END、BEGIN/END嵌套不配对）、语句末尾漏写分号、块结束后未加执行斜杠/导致的，下面的代码可以直接运行。

第一步：创建日志表log_table #

以下写法适配Oracle 12c及以上版本的原生自增主键，11g及更早版本可以通过序列+触发器默认值实现id自增。

CREATE TABLE log_table (
    id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    column_name VARCHAR2(32) NOT NULL,
    old_value VARCHAR2(4000),
    new_value VARCHAR2(4000),
    fakturanr NUMBER NOT NULL,
    date_of_change DATE DEFAULT SYSDATE NOT NULL
);

第二步：创建行级触发器 #

触发器同时监听INSERT、UPDATE操作，逐字段判断值是否变化，每个变更字段单独生成一条日志记录：

CREATE OR REPLACE TRIGGER ChangeOnFaktura
AFTER INSERT OR UPDATE ON faktura
FOR EACH ROW
BEGIN
    -- 处理INSERT操作：记录所有插入的非空字段
    IF INSERTING THEN
        IF :NEW.extnr IS NOT NULL THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('extnr', NULL, :NEW.extnr, :NEW.fakturanr);
        END IF;

        IF :NEW.fakturanr IS NOT NULL THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('fakturanr', NULL, :NEW.fakturanr, :NEW.fakturanr);
        END IF;

        IF :NEW.fakturadate IS NOT NULL THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('fakturadate', NULL, TO_CHAR(:NEW.fakturadate, 'yyyy-mm-dd hh24:mi:ss'), :NEW.fakturanr);
        END IF;

        IF :NEW.partner_name IS NOT NULL THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('partner_name', NULL, :NEW.partner_name, :NEW.fakturanr);
        END IF;
    END IF;

    -- 处理UPDATE操作：仅记录值实际发生变更的字段
    IF UPDATING THEN
        -- extnr字段变更判断，兼容NULL值场景
        IF :OLD.extnr <> :NEW.extnr 
            OR (:OLD.extnr IS NULL AND :NEW.extnr IS NOT NULL) 
            OR (:OLD.extnr IS NOT NULL AND :NEW.extnr IS NULL) THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('extnr', :OLD.extnr, :NEW.extnr, :NEW.fakturanr);
        END IF;

        -- fakturadate字段变更判断
        IF :OLD.fakturadate <> :NEW.fakturadate 
            OR (:OLD.fakturadate IS NULL AND :NEW.fakturadate IS NOT NULL) 
            OR (:OLD.fakturadate IS NOT NULL AND :NEW.fakturadate IS NULL) THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('fakturadate', TO_CHAR(:OLD.fakturadate, 'yyyy-mm-dd hh24:mi:ss'), TO_CHAR(:NEW.fakturadate, 'yyyy-mm-dd hh24:mi:ss'), :NEW.fakturanr);
        END IF;

        -- partner_name字段变更判断
        IF :OLD.partner_name <> :NEW.partner_name 
            OR (:OLD.partner_name IS NULL AND :NEW.partner_name IS NOT NULL) 
            OR (:OLD.partner_name IS NOT NULL AND :NEW.partner_name IS NULL) THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('partner_name', :OLD.partner_name, :NEW.partner_name, :NEW.fakturanr);
        END IF;

        -- 若需要监听fakturanr主键更新，放开下方注释即可
        /*
        IF :OLD.fakturanr <> :NEW.fakturanr 
            OR (:OLD.fakturanr IS NULL AND :NEW.fakturanr IS NOT NULL) 
            OR (:OLD.fakturanr IS NOT NULL AND :NEW.fakturanr IS NULL) THEN
            INSERT INTO log_table(column_name, old_value, new_value, fakturanr)
            VALUES ('fakturanr', :OLD.fakturanr, :NEW.fakturanr, :NEW.fakturanr);
        END IF;
        */
    END IF;
END;
/

代码末尾的/是Oracle客户端执行PL/SQL块的必需标识，漏写会导致块不提交执行，是触发PLS-00103错误的最常见原因。

关键逻辑说明 #

• 监听范围控制：AFTER INSERT OR UPDATE ON faktura表示同时监听表上的插入、更新全操作，如果需要只监听特定列的更新，可以写成AFTER INSERT OR UPDATE OF extnr, fakturadate ON faktura，只有指定列更新时触发器才会生效。
• 新旧值引用：行级触发器中:old代表操作前的行记录，:new代表操作后的行记录；INSERT操作只有:new值，DELETE操作只有:old值，UPDATE操作两个值都存在。判断字段变更时必须单独处理NULL值场景，否则NULL和非NULL值的变更会被漏记。
• 关联fakturanr：直接取:NEW.fakturanr即可，不管是插入还是更新，操作后的fakturanr都存在于:new记录中。
• 视图操作覆盖：因为view_faktura是可更新视图，USER1对视图的UPDATE最终会转换为对基表faktura的UPDATE，会正常触发上面创建的基表触发器，不需要额外给视图创建INSTEAD OF触发器。
• 权限说明：触发器以基表属主的权限执行，不需要给USER1额外授予log_table的写入权限，USER1仅持有视图UPDATE权限即可正常触发日志记录。

功能验证 #

执行以下测试语句后查询log_table，即可看到全路径的操作日志：

-- 测试基表插入
INSERT INTO faktura(extnr, fakturanr, fakturadate, partner_name)
VALUES ('TEST001', 1001, SYSDATE, '测试客户');

-- 测试基表更新
UPDATE faktura SET extnr = 'TEST001_UPD' WHERE fakturanr = 1001;

-- 测试视图更新
UPDATE view_faktura SET fakturadate = SYSDATE-1 WHERE fakturanr = 1001;

-- 查询日志记录
SELECT * FROM log_table ORDER BY id;

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