Error Trigger AFTER DELETE

Question

Error Trigger AFTER DELETE

Navigation

#1 by (1 votes)

0

I have the following code:

create or replace trigger calckmdl
after delete on tsegmento
for each row
declare
    total rutas.km%type;
begin
if deleting then
    select km into total from rutas where ruta=:new.ruta;
        if :old.unidad = 'km' then
            total:=total-:old.distancia;
            end if;
        else if :old.unidad = 'm' then
            total:=total-:old.distancia/1000;
        end if;
    update rutas set km=total where ruta=:new.ruta;
end if

When I make a DELETE, I always miss the error "No data found" . I've been breaking my head for weeks, I do not understand what the problem is. I have a trigger for INSERT and UPDATE of the same style and they work perfectly.

sql oracle plsql trigger

asked by ats93 26.06.2018 в 02:30

source

1 answer

Problems with a trigger in ORACLE How can I redirect to another external page?

score 1 · Accepted Answer

The first thing you should do whenever you do a select as you are doing is control the exceptions. If you do not want to try to at least put a NULL in the exception others.

BEGIN
   select km into total from rutas where ruta=:new.ruta;
EXCEPTION
   WHEN OTHERS THEN
      NULL;
END;

If the trigger is after delete it will only enter when you do a delete, so checking the first if is redundant.

If you are deleting you should use: old.route in the select, right ?, there is no new value in an erase.

Then I see that you have an END IF after the assignment TOTAL: = TOTAL -: OLD.DISTANCIA; so the IF: OLD.UNIDAD = 'm' THEN belongs to the else of the main IF, not the one of the unit check, so it would never fit in that part.

Let's see if what I've told you serves you