Forum Stats

  • 3,839,012 Users
  • 2,262,438 Discussions
  • 7,900,836 Comments

Discussions

Trigger -- invalid identifier?

606275
606275 Member Posts: 141
edited Sep 16, 2009 10:50AM in SQL & PL/SQL
Why am I receiving the following error for the following trigger?

"PL/SQL: ORA-00904: "N_ROW"."ID":invalid identifier

CREATE TRIGGER DELETE_ROW
AFTER INSERT ON SCHEMA1.TABLE1 REFERENCING NEW AS N_ROW
FOR EACH ROW
WHEN (N_ROW.FILE_SIZE > 2000000)
BEGIN
DELETE FROM SCHEMA1.TABLE1 WHERE ID = N_ROW.ID;
END;


Note: the column "ID" does exist in the referenced table
Tagged:

Best Answer

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    AFTER INSERT ON SCHEMA1.TABLE1 REFERENCING NEW AS N_ROW 
    FOR EACH ROW
    WHEN (N_ROW.FILE_SIZE > 2000000)
    BEGIN
    DELETE FROM SCHEMA1.TABLE1 WHERE ID = :N_ROW.ID; 
    END;
    why would you change the name of NEW to N_ROW? I never understood that...
«13

Answers

  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    AFTER INSERT ON SCHEMA1.TABLE1 REFERENCING NEW AS N_ROW 
    FOR EACH ROW
    WHEN (N_ROW.FILE_SIZE > 2000000)
    BEGIN
    DELETE FROM SCHEMA1.TABLE1 WHERE ID = :N_ROW.ID; 
    END;
    why would you change the name of NEW to N_ROW? I never understood that...
  • 673860
    673860 Member Posts: 902
    when you are referencing you have to use like this

    CREATE TRIGGER DELETE_ROW
    AFTER INSERT ON SCHEMA1.TABLE1 REFERENCING NEW AS N_ROW
    FOR EACH ROW
    WHEN (N_ROW.FILE_SIZE > 2000000)
    BEGIN
    DELETE FROM SCHEMA1.TABLE1 WHERE ID = :N_ROW.ID;
    END;
    673860
  • 493827
    493827 Member Posts: 161
    me too! makes things much more confusing.
  • 606275
    606275 Member Posts: 141
    Thanks - missed the ":".
    Now I shall know! :)
  • 606275
    606275 Member Posts: 141
    Actually - even after adding the : (as you did in your post) -- I still receive this error.
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    that's because you didn't reward points ;)
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    Not in my database
    SQL> create table table1
      2  (id number
      3  ,file_size number
      4  )
      5  /
    
    Table created.
    
    SQL> 
    SQL> create or replace trigger trg
      2  AFTER INSERT ON TABLE1 REFERENCING NEW AS N_ROW 
      3  FOR EACH ROW
      4  WHEN (N_ROW.FILE_SIZE > 2000000)
      5  BEGIN
      6  DELETE FROM TABLE1 WHERE ID = :N_ROW.ID; 
      7  END;
      8  /
    
    Trigger created.
    
    SQL> 
  • 606275
    606275 Member Posts: 141
    Yes - you are right! It is b/c I didn't reward any points ;)
    I actually had to drop the trigger and re-run the script & that worked.
    Thanks!
  • 673860
    673860 Member Posts: 902
    you should have a habbit of including replace keyword while creating procedure or trigger as
    CREATE or replace TRIGGER  DELETE_ROW   -- use or replace here
    AFTER INSERT ON SCHEMA1.TABLE1 REFERENCING NEW AS N_ROW
    FOR EACH ROW
    WHEN (N_ROW.FILE_SIZE > 2000000)
    BEGIN
    DELETE FROM SCHEMA1.TABLE1 WHERE ID = N_ROW.ID;
    END;
  • 666352
    666352 Member Posts: 1,442
    Hi Alex,

    And after you got ORA-04091:
    SQL> DROP TABLE table1;
    
    Table supprimée.
    
    SQL> CREATE TABLE table1
      2     (ID NUMBER
      3     ,file_size NUMBER
      4      );
    
    Table créée.
    
    SQL> 
    SQL> CREATE OR REPLACE TRIGGER trg
      2     AFTER INSERT
      3     ON table1
      4     REFERENCING NEW AS n_row
      5     FOR EACH ROW
      6     WHEN (n_row.file_size > 1)
      7  BEGIN
      8     DELETE FROM table1
      9           WHERE ID = :n_row.ID;
     10  END;
     11  /
    
    Déclencheur créé.
    
    SQL> INSERT INTO table1
      2       VALUES (1, 2);
    INSERT INTO table1
                      *
    ERREUR à la ligne 1 :
    ORA-04091: la table SCOTT.TABLE1 est en mutation ; le déclencheur ou la fonction ne peut la voir
    ORA-06512: à "SCOTT.TRG", ligne 2
    ORA-04088: erreur lors d'exécution du déclencheur 'SCOTT.TRG'
    
    
    SQL> 
This discussion has been closed.