Oracle触发器编译报错：SQL Statement ignored与ORA-00984排查

问题场景 #

尝试编译以下Oracle触发器时出现错误：

CREATE OR REPLACE TRIGGER myschema.my_trigger AFTER
    INSERT OR UPDATE ON myschema.my_table
    REFERENCING
            NEW AS new
            OLD AS old
    FOR EACH ROW
DECLARE
    cat_id NUMBER(38, 0);
BEGIN
    SELECT
        the_cat_id
    INTO cat_id
    FROM
        myschema.some_random_table cfg
    WHERE
        cfg.sn = :new.sn;

    IF inserting THEN
        INSERT INTO myschema.target_table (
            cat_id,
            sn,
            ch,
            cc,
            amnt
        ) VALUES (
            cat_id,
            :new.sn,
            :new.ch,
            :new.cc,
            :new.amnt
        );

    END IF;

    IF updating THEN
        UPDATE myschema.target_table
        SET
            rsrv_amnt = :new.amnt
        WHERE
                cc = :new.cc
            AND ch = :new.ch
            AND sn = :new.sn;

    END IF;

END;

SQL Developer返回的错误信息：

Trigger my_trigger compiled

LINE/COL  ERROR
--------- -------------------------------------------------------------
13/9      PL/SQL: SQL Statement ignored
20/13     PL/SQL: ORA-00984 column not allowed here
Errors: check compiler log

错误原因 #

ORA-00984错误的核心是变量名与目标表的列名重名：
你在DECLARE部分定义了变量cat_id，而myschema.target_table恰好也有一个同名的cat_id列。在INSERT语句的VALUES子句中使用cat_id时，Oracle会优先将其解析为列名，而非你定义的局部变量，这就导致了“列不允许出现在此处”的错误。

解决方案 #

修改局部变量的名称，避免与列名冲突，通常可以给变量加前缀（比如v_标识变量）。修改后的触发器代码如下：

CREATE OR REPLACE TRIGGER myschema.my_trigger AFTER
    INSERT OR UPDATE ON myschema.my_table
    REFERENCING
            NEW AS new
            OLD AS old
    FOR EACH ROW
DECLARE
    v_cat_id NUMBER(38, 0); -- 修改变量名，避免与列名冲突
BEGIN
    SELECT
        the_cat_id
    INTO v_cat_id
    FROM
        myschema.some_random_table cfg
    WHERE
        cfg.sn = :new.sn;

    IF inserting THEN
        INSERT INTO myschema.target_table (
            cat_id,
            sn,
            ch,
            cc,
            amnt
        ) VALUES (
            v_cat_id, -- 使用修改后的变量名
            :new.sn,
            :new.ch,
            :new.cc,
            :new.amnt
        );

    END IF;

    IF updating THEN
        UPDATE myschema.target_table
        SET
            rsrv_amnt = :new.amnt
        WHERE
                cc = :new.cc
            AND ch = :new.ch
            AND sn = :new.sn;

    END IF;

END;

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