Problem with trigger in oracle

Question

I'm new in oracle and i don't know what is wrong with this trigger:

CREATE OR REPLACE TRIGGER  "propuesta_casas" 
BEFORE INSERT ON "PROPUESTA_TIENDA_BARRIO"
FOR EACH ROW
 WHEN (new."CASASCAL" IS NULL) 
 BEGIN
    SELECT PROPUESTA.CASAS
    INTO :new."CASASCAL"
    FROM PROPUESTA WHERE PROPUESTA.IDPROPUESTA=new.IDPROPUESTA ;
 END;
/

Error:

PL/SQL: ORA-00904: "NEW"."IDPROPUESTA": identifider not valid

Answer 1

From what you described:

Try to recompile the trigger and see what happens...

A trigger becomes invalid if the base object (ex..table) becomes invalid or altered and the trigger refers to the affected table.

Answer 2

Not sure why the accepted answer has been accepted as neither it nor the attached comments seem to address the obvious issue in the posted code.

In a trigger body we reference values in the inserted row with the :NEW code word. The posted code lacks the colon when it references the column in the WHERE clause. This is what is needed:

CREATE OR REPLACE TRIGGER  "propuesta_casas" 
BEFORE INSERT ON "PROPUESTA_TIENDA_BARRIO"
FOR EACH ROW
 WHEN (new."CASASCAL" IS NULL) 
 BEGIN
    SELECT PROPUESTA.CASAS
    INTO :new."CASASCAL"
    FROM PROPUESTA 
    WHERE PROPUESTA.IDPROPUESTA=:new.IDPROPUESTA ;
 END;
/

---

Incidentally, watch out for using lower case in double quotes when creating objects.

By default all Oracle names are stored in the data dictionary in upper case, but the SQL statements are case insensitive. So the following two statments refer to the same object:

select * from emp
/
select * from EMP
/

However, if we create our object with a name in mixed case or lower case and put it in double quotes it is stored in the data dictionary with that exact case. This means we have to use that exact case whenever we reference the object, in double quotes. So if we created a table with all lower case ...

create table "emp"  ...

... then this statement will fail:

select * from emp
/

It has to be

select * from "emp"
/

Of course if we already have a table called EMP then the first statement would have succeeded, if would just have selected from a different table.

In the case of triggers we generally don't refer to them by name. But we have to use the case whenever we look up the trigger in the data dictionary:

select status
from user_triggers
where trigger_name = 'propuesta_casas'
/