Oracle Trigger - SQL Statement ignored and ORA-00984-CodePudding

I am trying to compile the below trigger in SQL Developer with a couple of errors.

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 shows the below error.

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

Can someone please tell me what I am doing wrong? Note: I have deliberately masked the Real column and table names and this is the first trigger I am writing in Oracle.

CodePudding user response：

If tables you use in that trigger really use columns you posted, then the trigger compiles:

SQL> create table some_random_table (the_cat_id number, cfg number, sn number);

Table created.

SQL> create table target_table(cat_id number, sn number, ch number, cc number, amnt number, rsrv_amnt number);

Table created.

SQL> create table my_table (sn number, ch number, cc number, amnt number);

Table created.

Trigger:

SQL> CREATE OR REPLACE TRIGGER my_trigger AFTER
  2      INSERT OR UPDATE ON my_table
  3      REFERENCING
  4              NEW AS new
  5              OLD AS old
  6      FOR EACH ROW
  7  DECLARE
  8      cat_id NUMBER(38, 0);
  9  BEGIN
 10      SELECT
 11          the_cat_id
 12      INTO cat_id
 13      FROM
 14          some_random_table cfg
 15      WHERE
 16          cfg.sn = :new.sn;
 17
 18      IF inserting THEN
 19          INSERT INTO target_table (
 20              cat_id,
 21              sn,
 22              ch,
 23              cc,
 24              amnt
 25          ) VALUES (
 26              cat_id,
 27              :new.sn,
 28              :new.ch,
 29              :new.cc,
 30              :new.amnt
 31          );
 32
 33      END IF;
 34
 35      IF updating THEN
 36          UPDATE target_table
 37          SET
 38              rsrv_amnt = :new.amnt
 39          WHERE
 40                  cc = :new.cc
 41              AND ch = :new.ch
 42              AND sn = :new.sn;
 43
 44      END IF;
 45
 46  END;
 47  /

Trigger created.

SQL>

You said:

Note: I have deliberately masked the Real column and table names

In that case, you can't really expect us to blindly guess what you did wrong. Could be anything (any column and/or any table).