Trigger -- invalid identifier?

606275

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

21205

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

21205

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

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;

493827

me too! makes things much more confusing.

606275

Thanks - missed the ":".
Now I shall know!

606275

Actually - even after adding the : (as you did in your post) -- I still receive this error.

21205

that's because you didn't reward points

21205

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

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

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

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>