PL/SQL Error ORA-01403 no data found

Question

I'm recieving the ORA-01403 no data found error when trying to insert in a table after creating the following trigger:

CREATE OR REPLACE TRIGGER unic_disc
BEFORE insert ON disciplina
FOR EACH ROW
DECLARE
    CURSOR cursor_professor IS 
    SELECT matricula_professor
    FROM disciplina;
    temp_prof disciplina.matricula_professor%type;
BEGIN
    OPEN cursor_professor;
    FETCH cursor_professor INTO temp_prof;
    CLOSE cursor_professor;
END;
/

(the variables are in portugues, but their name does not interfere in the logic.) the table creation,

CREATE TABLE disciplina (
    codigo_disciplina NUMBER,
    ementa VARCHAR2(50) NOT NULL,
    conteudo_programatico VARCHAR2(100) NOT NULL,
    matricula_professor NUMBER NOT NULL,
    CONSTRAINT disciplina_pk PRIMARY KEY (codigo_disciplina),
    CONSTRAINT disciplina_matricula_prof_fk FOREIGN KEY (matricula_professor)         REFERENCES professor (matricula_professor)
);

My insert query:

INSERT INTO disciplina (codigo_disciplina,ementa,conteudo_programatico,matricula_professor) VALUES (7,'E6', 'C6',7777);

EDIT: I think the error is because I am selecting from the same table I'm editing.

Are you sure you want to use the cursor this way and not iterate over it? — Armunin
There are other triggers and the actual trigger I'm using is more complicated, I have simplified it for the purpose of finding the error. In the actual trigger, I iterate over the cursor. — rvcam

rvcam rvcam · Accepted Answer · 2013-12-23T04:24:14

I just needed to add PRAGMA_AUTONOMOUS_TRANSACTION after the DECLARE AND IT WORKED. I couldn't find it on my own, a friend told me.

PL/SQL Error ORA-01403 no data found

2 Answers