如何通过触发器在更新操作中提取主键列名与值？

错误原因分析 #

你遇到的PLS-00049错误主要来自三个核心问题：

1. 动态SQL语法错误：原代码构造的SELECT :1.' || identifierColumn || 'FROM EMPLOYEE缺少列名与FROM之间的空格，且完全没必要查询原表——触发器的:OLD/:NEW伪记录已经包含当前行的所有数据，无需额外查询。
2. 动态列名引用错误：直接使用:OLD.identifierColumn不合法，因为identifierColumn是运行时才确定的变量，PL/SQL编译阶段无法解析这个绑定变量。
3. 主键值未赋值：DELETE和INSERT分支中identifierValue未赋值，导致后续调用存储过程时传入空值。

修正后的触发器代码 #

create or replace TRIGGER EMPLOYEE_TGR 
AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEE 
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 
    tableName VARCHAR2(40) := 'EMPLOYEE';
    actionType VARCHAR2(1);
    identifierColumn VARCHAR2(40);
    identifierValue VARCHAR2(650);
BEGIN
    -- 获取表的主键列名
    SELECT cols.column_name 
    INTO identifierColumn 
    FROM all_constraints cons 
    JOIN all_cons_columns cols ON cons.constraint_name = cols.constraint_name 
    WHERE cons.table_name = UPPER(tableName) 
      AND cons.constraint_type = 'P' 
      AND ROWNUM = 1;

    -- 判断操作类型并提取主键值
    IF DELETING THEN
        actionType := 'D';
        EXECUTE IMMEDIATE 'SELECT :rec.' || identifierColumn || ' FROM DUAL' 
        INTO identifierValue 
        USING :OLD;
    ELSIF UPDATING THEN
        actionType := 'U';
        EXECUTE IMMEDIATE 'SELECT :rec.' || identifierColumn || ' FROM DUAL' 
        INTO identifierValue 
        USING :OLD;

        -- 检查NAME字段变化
        IF :OLD.NAME != :NEW.NAME THEN
            PROC_INSERT_IN_AUDIT (tableName, :OLD.LAST_UPDATE_DATE, :OLD.LAST_UPDATE_BY, 
                'Y', actionType, identifierColumn, identifierValue, 
                'NAME', :OLD.NAME, :NEW.NAME);
        END IF;

        -- 检查URI字段变化
        IF :OLD.URI != :NEW.URI THEN
            PROC_INSERT_IN_AUDIT (tableName, :OLD.LAST_UPDATE_DATE, :OLD.LAST_UPDATE_BY, 
                'Y', actionType, identifierColumn, identifierValue, 
                'URI', :OLD.URI, :NEW.URI);
        END IF;

        -- 检查SCENARIO_CODE字段变化
        IF :OLD.SCENARIO_CODE != :NEW.SCENARIO_CODE THEN
            PROC_INSERT_IN_AUDIT (tableName, :OLD.LAST_UPDATE_DATE, :OLD.LAST_UPDATE_BY, 
                'Y', actionType, identifierColumn, identifierValue, 
                'SCENARIO_CODE', :OLD.SCENARIO_CODE, :NEW.SCENARIO_CODE);
        END IF;

        -- 检查USE_CASE_CODE字段变化（修正原代码参数错误）
        IF :OLD.USE_CASE_CODE != :NEW.USE_CASE_CODE THEN
            PROC_INSERT_IN_AUDIT (tableName, :OLD.LAST_UPDATE_DATE, :OLD.LAST_UPDATE_BY, 
                'Y', actionType, identifierColumn, identifierValue, 
                'USE_CASE_CODE', :OLD.USE_CASE_CODE, :NEW.USE_CASE_CODE);
        END IF;
    ELSE
        actionType := 'I';
        EXECUTE IMMEDIATE 'SELECT :rec.' || identifierColumn || ' FROM DUAL' 
        INTO identifierValue 
        USING :NEW;
    END IF;

    -- 处理DELETE和INSERT的审计插入
    IF actionType IN ('D', 'I') THEN
        PROC_INSERT_IN_AUDIT (tableName, 
            CASE actionType WHEN 'D' THEN :OLD.LAST_UPDATE_DATE ELSE :NEW.LAST_UPDATE_DATE END,
            CASE actionType WHEN 'D' THEN :OLD.LAST_UPDATE_BY ELSE :NEW.LAST_UPDATE_BY END,
            'Y', actionType, identifierColumn, identifierValue, NULL, NULL, NULL);
    END IF;

END EMPLOYEE_TGR;

关键修正点说明 #

• 动态提取主键值：通过EXECUTE IMMEDIATE 'SELECT :rec.' || identifierColumn || ' FROM DUAL'绑定:OLD或:NEW伪记录，动态获取主键列的值，规避编译时的绑定变量错误。
• 移除冗余查询：直接使用触发器提供的伪记录，不再查询EMPLOYEE表，提升性能并避免并发问题。
• 修正字段判断错误：将原代码中笔误的:OLD.identifierColumn != :NEW.NAME改为:OLD.NAME != :NEW.NAME；同时修正最后一个IF中的参数错误（将SCENARIO_CODE改为USE_CASE_CODE）。
• 统一主键值赋值：在所有操作分支中为identifierValue赋值，确保后续存储过程调用的参数合法性。

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