In my database I have some procedures and some triggers in the table relation. I want that when I finish making a loan, that is, when I update the return date ('Fecha_Devolución')
, my serial number that is a primary value, can change its status and the amount of my item ('cod_t_elemento')
can increase in 1
, but I have problems when I try to update the return date, since the database says that my table 'Prestamo'
is mutating.
I would greatly appreciate any help regarding this error.
EDIT:
This is the trigger
with which the process would end.
create or replace TRIGGER FINALIZAR_PRESTAMO
AFTER UPDATE of FECHA_DEVOLUCION ON PRESTAMO
FOR EACH ROW
DECLARE
MI_SERIAL VARCHAR2(30) := '';
MI_ELEMENTO NUMBER;
BEGIN
SELECT ENTREGA.NUMERO_SERIAL INTO MI_SERIAL FROM ENTREGA, ELEMENTO, PRESTAMO
Where ENTREGA.numero_serial = ELEMENTO.num_serial AND ENTREGA.COD_PRESTAMO = PRESTAMO.COD_PRESTAMO;
SELECT ENTREGA.COD_T_ELEMENTO INTO MI_ELEMENTO FROM ENTREGA, TIPO_ELEMENTO, PRESTAMO
Where ENTREGA.cod_t_elemento = TIPO_ELEMENTO.COD_T_ELEMENTO AND ENTREGA.COD_PRESTAMO = PRESTAMO.COD_PRESTAMO;
IF NOT (:NEW.FECHA_DEVOLUCION is NULL) THEN
CAMBIAR_A_FUNCIONAL(MI_SERIAL);
aumentar_elemento_dado_p(mi_elemento);
imprimir('Prestamo finalizado con exito');
else
imprimir('Error al finalizar prestamo');
end if;
end;
EDIT 2:
Thanks for the prompt response, I read the linked question and try the proposed solution to change the trigger to BEFORE UPDATE
but I still have the same error of a mutant table; I think the problem can occur in the SELECT
sentences but I'm not sure, and the procedures called CAMBIAR_A_FUNCIONAL
and AUMENTAR_ELEMENTO_DADO_P
do not perform any interaction with the table PRESTAMO
.