Problems creating Trigger ORA-00942

1.5k views Asked by At

I'm trying to create a trigger that should verify a new input value for 'grado_academico' when I insert into 'Investigadores' that is in 'director' schema and in another tablespace.

This is my table

CREATE TABLE director.Investigadores (
id_investigador numeric(6, 0) NOT NULL,
 nombre varchar(40)  NOT NULL, apellido_pat varchar(40)  NOT NULL,
 apellido_mat varchar(40)  NOT NULL, grado_academico varchar(40)  NOT NULL, 
 fecha_ingreso date NOT NULL,  nivel_sni numeric(2, 0) NOT NULL,  
 CONSTRAINT id_investigador  PRIMARY KEY (id_investigador)) 
 tablespace sni_tablespace;

and this is my trigger

  CREATE OR REPLACE TRIGGER verificarGrado
        before insert on director.Investigadores 
        for each row
        begin
            IF (new.grado_academico='MC' OR new.grado_academico='DOCTOR' OR new.grado_academico='LICENCIATURA')
                return new;
            else
                RAISE EXCEPTION 'El grado academico solo puede ser MC, DOCTOR o LICENCIATURA';
            end if;
        END;
/

But when i created the trigger returns me a warning, so I run

SQL> show errors
ERROR:
ORA-00942: table or view does not exist

So why it returns me this warning if my table already exist?
is my trigger incorrectly?
My user 'director' has privileges to create any trigger.
Should I do something more?
Please I hope you could help me with this issue.

EDIT

I changed my trigger using the schema

CREATE OR REPLACE TRIGGER director.verificarGrado
before insert on director.Investigadores
for each row
begin
    IF (:new.grado_academico='MC' OR :new.grado_academico='DOCTOR' OR :new.grado_academico='LICENCIATURA')
    then
            :new.grado_academico:=:new.grado_academico; 
    else
        RAISE EXCEPTION 'El grado academico solo puede ser MC, DOCTOR o LICENCIATURA';
    end if;
END;/

Throws me the same error.

The line :new.grado_academico:=:new.grado_academico; is where I'm not sure what i suppossed to do, i just need that the insertion keeps going

EDIT I changed the logic for my conditional I'm using IF NOT ... thank you for that recommendation

Anyways, It looks it's a issue about privileges, I still don't know how to solve it.

0

There are 0 answers