ORA-01403 no data found in trigger execution

Question

I have a problem here with a trigger. The purpose of the trigger is to verify that the client associated with a car registration, paid or not paid the bill in a workshop. If the client has paid all, then it is created a new service order (so far runs), but then paid the account is not possible to create a work order. So here the problem arises when I try to insert a service.

This is what's causing the trigger to fire:

INSERT INTO ORDEM(cod_ordem,data,codigo_func_m,tipo_ordem,matricula,estado_ordem)
VALUES(to_char(seq_cod_ordem.nextval),to_date('23/11/2014','dd/mm/yyyy'),'2','Serviço','66-AB-00','Pendente')

and I get this error after the execution:

Error starting at line : 140 in command -
INSERT INTO ORDEM(cod_ordem,data,codigo_func_m,tipo_ordem,matricula,estado_ordem)
VALUES(to_char(seq_cod_ordem.nextval),to_date('23/11/2014','dd/mm/yyyy'),'2','Serviço','66-AB-00','Pendente')
Error report -
SQL Error: ORA-01403: não foram encontrados dados
ORA-06512: na "BD1415_DC5.SALDO_CLIENTE_OFICINA", linha 7
ORA-04088: erro durante a execução do trigger 'BD1415_DC5.SALDO_CLIENTE_OFICINA'
01403. 00000 -  "no data found"
*Cause:    
*Action:

This is my code:

create or replace TRIGGER saldo_cliente_Oficina
BEFORE INSERT ON ORDEM
FOR EACH ROW
DECLARE
   t_codigo_cliente CLIENTES.codigo_cliente%TYPE;
   t_estado BOOLEAN := TRUE;
   t_excecao EXCEPTION;

BEGIN
  SELECT DISTINCT codigo_cliente INTO t_codigo_cliente
  FROM ORDEM, VEICULO
  WHERE (ORDEM.matricula = :NEW.matricula) AND (ORDEM.matricula = VEICULO.matricula);

FOR t_estado_fact IN (SELECT FACTURA.numero_factura,FACTURA.codigo_cliente,FACTURA.estado FROM                  FACTURA,CLIENTES
      WHERE CLIENTES.codigo_cliente = t_codigo_cliente AND CLIENTES.codigo_cliente =    FACTURA.codigo_cliente)LOOP
      IF t_estado_fact.estado = 'Não Paga' THEN
          t_estado := FALSE;
      END IF;    

      IF t_estado = FALSE THEN
        RAISE t_excecao;
      END IF;  
END LOOp;

EXCEPTION
  WHEN t_excecao THEN
    RAISE_APPLICATION_ERROR(-20001, 'O Proprietário do veiculo que pretende criar uma ordem, deve serviços a Oficina.');

END saldo_cliente_Oficina;

possible duplicate of [PLSQL Trigger ORA 01403 no data found](http://stackoverflow.com/questions/20160040/plsql-trigger-ora-01403-no-data-found) — Ben, Nov 25 '14 at 13:01
The first query 'SELECT DINSTINCT ... INTO ...' returns no rows and this is a reason of the exception. You need to enclose the query within 'BEGIN ... EXCEPTION NO_DATA_FOUND... END' block to catch this exception and do something with it — krokodilko, Nov 25 '14 at 17:15

score 1 · Accepted Answer · answered Nov 25 '14 at 17:09

1

The below select in the trigger is returning no rows.In this case the first insert in ORDEM for a particular matricula will always fail.

 SELECT DISTINCT codigo_cliente INTO t_codigo_cliente
 FROM ORDEM, VEICULO
 WHERE (ORDEM.matricula = :NEW.matricula) AND (ORDEM.matricula = VEICULO.matricula);

Kindly try changing this to the below

 SELECT DISTINCT codigo_cliente INTO t_codigo_cliente
 FROM VEICULO
 WHERE VEICULO.matricula=:NEW.matricula;

answered Nov 25 '14 at 17:09

psaraj12

4,772
2
21
30

Thank you. Even that was the problem. I had experienced the select's before starting to implement the trigger, then I copy paste ... Thanks for the help :) – Pedro Vieira Nov 25 '14 at 18:54

ORA-01403 no data found in trigger execution

1 Answers1