Oracle视图触发器问题：基表未自动添加USER/TIME审计字段

原触发器的核心问题 #

1. 触发范围受限：这是视图的INSTEAD OF触发器，仅当通过视图EMP_VIEWDOP5执行增删改操作时才会触发，直接操作基表EMPLOYEESDOP5不会触发该逻辑，因此无法自动添加审计字段或更新记录。
2. 冗余的自治事务：PRAGMA AUTONOMOUS_TRANSACTION会让表结构修改和后续数据操作在独立事务中执行，可能导致表结构变更提交后，数据操作回滚，出现结构变更无法回滚的不一致问题。
3. 未覆盖MERGE操作：原触发器未处理MERGE场景，而MERGE会同时触发INSERTING和UPDATING状态。

修正后的视图INSTEAD OF触发器 #

CREATE OR REPLACE TRIGGER audit_changes_trg
INSTEAD OF INSERT OR UPDATE OR DELETE OR MERGE ON EMP_VIEWDOP5
DECLARE
    v_column_exists NUMBER;
BEGIN
    -- 检查基表是否已存在审计字段
    SELECT COUNT(*)
    INTO v_column_exists
    FROM user_tab_columns
    WHERE table_name = 'EMPLOYEESDOP5'
      AND column_name = 'USERAUDIT';

    -- 首次操作时添加审计字段
    IF v_column_exists = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE EMPLOYEESDOP5 ADD (
            USERaudit VARCHAR2(30),
            TIMEaudit TIMESTAMP
        )';
    END IF;

    -- 处理INSERT操作
    IF INSERTING THEN
        EXECUTE IMMEDIATE 
        'INSERT INTO EMPLOYEESDOP5 (EMP_ID, EMP_NAME, EMP_SALARY, DEPT_ID, USERaudit, TIMEaudit)
         VALUES (:1, :2, :3, :4, :5, :6)'
        USING :NEW.EMP_ID, :NEW.EMP_NAME, :NEW.EMP_SALARY, :NEW.DEPT_ID, USER, SYSTIMESTAMP;
    END IF;

    -- 处理UPDATE操作
    IF UPDATING THEN
        EXECUTE IMMEDIATE 
        'UPDATE EMPLOYEESDOP5 
         SET EMP_NAME = :1, 
             EMP_SALARY = :2, 
             DEPT_ID = :3,
             USERaudit = :4,
             TIMEaudit = :5
         WHERE EMP_ID = :6'
        USING :NEW.EMP_NAME, :NEW.EMP_SALARY, :NEW.DEPT_ID, USER, SYSTIMESTAMP, :OLD.EMP_ID;
    END IF;

    -- 处理DELETE操作
    IF DELETING THEN
        EXECUTE IMMEDIATE 
        'DELETE FROM EMPLOYEESDOP5 WHERE EMP_ID = :1'
        USING :OLD.EMP_ID;
    END IF;

    -- MERGE操作会自动触发INSERTING/UPDATING分支，上述逻辑已覆盖
END;
/

强制通过视图操作基表（可选） #

若要避免用户直接操作基表导致审计字段未更新，可给基表添加BEFORE触发器，阻止直接的DML操作：

CREATE OR REPLACE TRIGGER block_direct_emp_trg
BEFORE INSERT OR UPDATE OR DELETE ON EMPLOYEESDOP5
BEGIN
    RAISE_APPLICATION_ERROR(-20001, '请通过视图EMP_VIEWDOP5执行操作');
END;
/

关键说明 #

• 所有增删改操作必须通过视图EMP_VIEWDOP5执行，才能触发审计逻辑，自动添加字段并记录操作信息。
• 移除自治事务后，表结构修改和数据操作在同一事务中，确保操作的原子性。
• MERGE操作会自动触发对应分支逻辑，无需额外编写代码。

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