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!

Answer 1

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...)