views:

36

answers:

2

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

+2  A: 

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.

Leniel Macaferi
when i compile i get: was found EOF when weit begin case declare end exception exit for goto if loop mod null pragma raise return select update
diegueus9
You're getting this exact message? Isn't there an ORA-XXX error code?
Leniel Macaferi
I was missing the stament END, but now this is the new error:PL/SQL: ORA-00904: "NEW"."IDPROPUESTA": identifider not valid
diegueus9
I think you need to do this: new."IDPROPUESTA"
Leniel Macaferi
yes,that is right
diegueus9
+3  A: 

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'
/
APC