Error ORA-04088 using triger before UPDATE

Question

I ran into this error. I tried to explore other 04088 topics but I am still not able to fix it.

My trigger:

CREATE OR REPLACE TRIGGER "TU_INTEGRATIONS"
  BEFORE UPDATE ON INTEGRATIONS
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
DECLARE
  bckId     INT;
  SQLBody   VARCHAR2(500);
  tableName VARCHAR2(100);
BEGIN
  IF :OLD.BCK_ID <> :NEW.BCK_ID THEN
    DELETE INT_MODULES im
     WHERE im.INT_ID = :NEW.INT_ID
       AND NOT EXISTS (SELECT 1
                         FROM (SELECT BCKM_ID
                                 FROM BACKENDS
                                WHERE BACKENDS.BCK_ID = :NEW.BCK_ID) b
                         JOIN ROUTING_SEQUENCES rs
                           ON rs.BCKM_ID = b.BCKM_ID);
  END IF;

  FOR item IN (
               SELECT MOD_TABLE
                 FROM MODULES m
                WHERE MOD_LEV_INTEGRATION = 1
                  AND NOT EXISTS (SELECT 1
                         FROM INT_MODULES imi
                        WHERE imi.INT_ID = :NEW.INT_ID
                          AND imi.MOD_ID = m.MOD_ID)
               ) 
  LOOP
    SQLBody := 'DELETE ' || item.MOD_TABLE || ' WHERE INT_ID = ' ||TO_CHAR(:NEW.INT_ID);
    EXECUTE IMMEDIATE SQLBody;  
  END LOOP;
END "TU_INTEGRATIONS";

Tables used:

The error is:

ORA-00904: "INT_ID": invalid identifier ORA-06512: at "TU_INTEGRATIONS",
line 21 ORA-06512: at "TU_INTEGRATIONS", 
line 21 ORA-04088: error during execution of trigger 'TU_INTEGRATIONS'

I call merge method from entityManager on Integration object where I change just the INT_NAME column.

@Override
    public void createOrUpdate(T entity) {
        if (entity instanceof TimeTracked) {
            processAuditable((TimeTracked) entity);
        }
        T savedEntity = entityManager.merge(entity);
        entity.setId(savedEntity.getId());
    }

Thank you for your tips!

Please don't post the table descriptions as images; instead, post them as `CREATE TABLE` statements as formatted text that we can copy/paste as it makes it much easier to debug if we can easily re-create the problem. — MT0, Nov 22 '22 at 10:45
What are the table names stored in `modules.mod_table`, and do all of those tables have an `int_id` column? The error suggests not - it's coming from the `execute immediate` call. (For a trigger, the line number in a PL/SQL error starts from the PL/SQL trigger body - so here `DECLARE` is line 1, not line 4 - which can be confusing.) If you add `dbms_output.put_line(SQLBody);` before that, and run your merge from a client that can show the output, you can see which table it's actually erroring on. — Alex Poole, Nov 22 '22 at 10:51
_"DELETE INT_MODULES im"_, I think the 'FROM' keyword missing. That is why it's not recognize "INT_ID" and shows the invalid identifier error. — Sujay, Nov 22 '22 at 11:27
@Sujay - the `FROM` isn't required - it's shown as an optional branch on [the syntax diagram](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/DELETE.html). — Alex Poole, Nov 22 '22 at 13:32

score 2 · Answer 1 · answered Nov 22 '22 at 11:39

The problem isn't (directly) in the trigger code you posted; it's a run-time error that is coming from the dynamic SQL statement you are executing on line 21, which is where the error is reported (counting from the start of the PL/SQL trigger body, where DECLARE is line 1).

The issue is that your modules table has a row that is being included in the cursor query, but which has a mod_table value that refers to a table which does not have an int_id column.

To demonstrate if you have a row with mod_table set to 'TABLE_A' and that table is defined as:

create table table_a (int_id number);

then your code doesn't error; but if you change it to:

create table table_a (dummy number);

then it throws exactly the error you see.

fiddle

You either need to exclude tables which don't have that column form the query, or test for them before executing the delete. (Or revisit your logic...)

Error ORA-04088 using triger before UPDATE

1 Answers1