Oracle 12c通用UPDATE触发器创建问题求助

我正尝试为名为EMPLOYEES的表创建一个通用UPDATE触发器（“通用”指无需硬编码逐一检查每张表的列），该表以EMP_ID作为主键，包含多个列。当发生UPDATE操作时，希望将旧值存入名为EMPLOYEES_AUDITING的表，其结构为(EMPLOYEES_PK, ACTION_DT, UPDATED_FIELD, UPDATED_VALUE)。

当前实现代码如下：

CREATE OR REPLACE TRIGGER UPDATE_MONITOR 
BEFORE UPDATE ON EMPLOYEES

FOR EACH ROW
  
DECLARE  
SELECT column_name FROM dba_tab_columns WHERE table_name = 'EMPLOYEES';

v_name VARCHAR2 (100);

BEGIN

   OPEN coi;
   LOOP
     FETCH coi INTO v_name;
     EXIT WHEN coi% NOTFOUND;

     IF UPDATING(v_name) THEN 

       -- Insert old value in the EMPLOYEES_AUDITING table
        EXECUTE IMMEDIATE
        'INSERT INTO EMPLOYEES_AUDITING (EMPLOYEES_PK, ACTION_DT, UPDATED_FIELD, UPDATED_VALUE)
 VALUES (:OLD.EMP_ID, SYSDATE, '||v_name||', :OLD.'||v_name||')';

     END IF;
   END LOOP;
   
   CLOSE coi;

END;

问题在于代码中的:OLD.'||v_name||'部分无法正确解析为对应列的旧值，求解决思路或变通方法。

解决思路与实现 #

核心问题是动态SQL中无法直接引用:OLD伪记录的动态列名——:OLD属于PL/SQL上下文的绑定变量，不能在拼接的SQL字符串中直接解析。以下是两种可行的解决方案：

方法1：使用DBMS_SQL获取旧值 #

通过DBMS_SQL.COLUMN_VALUE直接提取:OLD记录的指定列值，避免动态SQL拼接的问题：

CREATE OR REPLACE TRIGGER UPDATE_MONITOR 
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
DECLARE
    CURSOR coi IS
        SELECT column_name 
        FROM dba_tab_columns 
        WHERE table_name = 'EMPLOYEES'
          AND owner = USER; -- 限定当前用户，避免跨用户表冲突
    v_name VARCHAR2(100);
    v_old_value VARCHAR2(4000); -- 若存在大字段或非字符类型，需调整类型/长度
BEGIN
    OPEN coi;
    LOOP
        FETCH coi INTO v_name;
        EXIT WHEN coi%NOTFOUND;
        
        IF UPDATING(v_name) THEN
            -- 直接获取:OLD的列值
            v_old_value := DBMS_SQL.COLUMN_VALUE(:OLD, v_name);
            
            -- 插入审计表，全程使用绑定变量避免SQL注入
            INSERT INTO EMPLOYEES_AUDITING (EMPLOYEES_PK, ACTION_DT, UPDATED_FIELD, UPDATED_VALUE)
            VALUES (:OLD.EMP_ID, SYSDATE, v_name, v_old_value);
        END IF;
    END LOOP;
    CLOSE coi;
END;
/

方法2：通过动态SQL预取旧值 #

先通过动态SQL获取:OLD的列值，再用绑定变量传入插入语句：

CREATE OR REPLACE TRIGGER UPDATE_MONITOR 
BEFORE UPDATE ON EMPLOYEES
FOR EACH ROW
DECLARE
    CURSOR coi IS
        SELECT column_name 
        FROM dba_tab_columns 
        WHERE table_name = 'EMPLOYEES'
          AND owner = USER;
    v_name VARCHAR2(100);
    v_old_value VARCHAR2(4000);
BEGIN
    OPEN coi;
    LOOP
        FETCH coi INTO v_name;
        EXIT WHEN coi%NOTFOUND;
        
        IF UPDATING(v_name) THEN
            -- 先执行动态SQL获取旧值
            EXECUTE IMMEDIATE 'SELECT :OLD.' || v_name || ' FROM DUAL'
            INTO v_old_value USING :OLD;
            
            -- 用绑定变量插入审计表
            EXECUTE IMMEDIATE
            'INSERT INTO EMPLOYEES_AUDITING (EMPLOYEES_PK, ACTION_DT, UPDATED_FIELD, UPDATED_VALUE)
             VALUES (:1, SYSDATE, :2, :3)'
            USING :OLD.EMP_ID, v_name, v_old_value;
        END IF;
    END LOOP;
    CLOSE coi;
END;
/

额外注意事项 #

1. 原代码未声明游标coi，需在DECLARE段明确定义。
2. 若表中包含数字、日期等非字符类型列，需将v_old_value转换为字符串（如用TO_CHAR）后存入审计表，或根据列类型调整变量类型。
3. 查询dba_tab_columns时添加owner = USER限定，避免误获取其他用户的同名表列。

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