Trigger Oracle se crea pero no Actualiza

[freddy31DA]

Hola buenas gente!:
Saludos a todos.
Estoy trantando de actualizar una tabla en Oracle 11g a treavés de un trigger. El trigger se compila bien. Pero cuando realizo el update en la tabla Prestamo se salta un error que ya vengo soportando:
La tabla:

Código sql:

CREATE TABLE Prestamo(
 idLector NUMERIC(10) NOT NULL,
 idLibro NUMERIC(10) NOT NULL, 
 FechaPrestamo DATE NOT NULL,
 FechaDev DATE  NULL,
 FechaRealDev DATE NULL,
 Mora NUMBER(19,4) NULL,
 estado NUMBER(1) NULL,
 CONSTRAINT idlectorPrestamo_fk FOREIGN KEY (idLector) REFERENCES Estudiante(idLector),
 CONSTRAINT idLibroPrestamo_fk FOREIGN KEY (idLibro) REFERENCES Libro(idLibro)
);
 

La cosa es así: Si un lector quiere entregar un libro y este se a pasado de la fecha se le suma 5 de Mora:
Registro 1:

Código sql:

INSERT INTO Prestamo VALUES (1,1,'9-6-2016','12-6-2016','','',0);
-- 0 = prestado
-- 1 entregado
--Devuelve: 
    UPDATE Prestamo SET estado=1 WHERE idLector=1 AND idLibro=1
 

Este es el trigger que e creado:

Código sql:

CREATE OR REPLACE TRIGGER TR_CALCULARMORA
    AFTER UPDATE 
     ON Prestamo
     FOR EACH ROW
    DECLARE
        dias INTEGER;
        fechaDevOriginal DATE;
    BEGIN
         SELECT fechaDev INTO fechaDevOriginal FROM Prestamo WHERE idLector=:OLD.idLector AND idLibro=:OLD.idLibro;
         SELECT trunc(sysdate) - to_date(fechaDevOriginal, 'dd-mm-yyyy') INTO dias FROM dual;
         IF dias > 3 THEN
          UPDATE Prestamo SET Mora=(dias-3)*5, FechaRealDev=sysdate 
          WHERE idLector=:OLD.idLector AND idLibro=:OLD.idLibro;
          END IF;
    END;
 

Como veran estoy tratando de actualizar la tabla Prestamo luego de la entrega - Ahí mismo debe de disparar este trigger comprobando si hay mora o no. Le resto 3 dias por que esos no cuentan solo en adelante. Con ese artilugio estoy contando cuantos dias han pasado luego de la fecha que quedo pendiente en la devolución.

El error que obtengo es:

Código sql:

UPDATE Prestamo SET estado=1 WHERE idLector=1 AND idLibro=1
Informe de error -
Error SQL: ORA-04091: TABLE ADMINORACLE11G.PRESTAMO IS mutating, TRIGGER/FUNCTION may NOT see it
ORA-06512: at "ADMINORACLE11G.TR_CALCULARMORA", line 5
ORA-04088: error during execution OF TRIGGER 'ADMINORACLE11G.TR_CALCULARMORA'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A TRIGGER (OR a USER defined plsql FUNCTION that IS referenced IN
           this statement) attempted TO look at (OR MODIFY) a TABLE that was
           IN the middle OF being modified BY the statement which fired it.
*Action:   Rewrite the TRIGGER (OR FUNCTION) so it does NOT READ that TABLE.
 

La verdad si alguien me puede ayudar con esto etare agradecido.
Gracias

[freddy31DA]

UPDATE: la condición es dias > 0 puesto que se coge la fecha quedo en devolver y demas ya no se resta solo directo Mora=dias*5 puesto que se coge la fechaDev

[AVBorland]

Hola!
No puede usted utilizar select, insert, update, delete una fila en tabla cuando realiza trigger de fila nivel (FOR EACH ROW) en esta tabla. Puede utilizar tabla rowindex y varios triggeros de operación nivel (solo before insert, update, delete y after insert, update, delete)

[AVBorland]

Iniciado por AVBorland

Puede utilizar tabla rowindex

Perdone, por favor...Tabla PL/SQL

[huesos52]

Efectivamente, no es posible hacer este tipo de operaciones sobre un trigger que afecta directamente la misma tabla.
A esto, se le conoce como tablas mutantes. Sin embargo, para lograr tu objetivo, pudes jugar un poco con las variables OLD y NEW.

Prueba este treigger:

Código SQL:

CREATE OR REPLACE TRIGGER TR_CALCULARMORA
BEFORE UPDATE
ON Prestamo
FOR EACH ROW
DECLARE
  dias INTEGER;
BEGIN
  DIAS := TRUNC(SYSDATE) - :OLD.FECHADEV;
  IF dias > 3 THEN
    :NEW.MORA:=(DIAS-3)*5;
    :NEW.FECHAREALDEV:=SYSDATE;         
  END IF;
END;
/
 

De esta forma, no se realizan operaciones sobre la tabla, solo se aprovechan los valores actuales reales de la tabla.

Nos cuentas

[AVBorland]

Iniciado por huesos52

Código SQL:

CREATE OR REPLACE TRIGGER TR_CALCULARMORA
BEFORE UPDATE
ON Prestamo
 

Por que BEFORE UPDATE?
Yo creo que:
Utilizar

Código SQL:

CREATE OR REPLACE package test_Package AS
  TYPE TTmpRec IS record (id_lib INTEGER, Mora INTEGER);
  TYPE TTmpTabla IS TABLE OF TTmpRec INDEX BY binary_integer;
  
  TempTabla TTmpTabla;
  Latch BOOLEAN:=FALSE;
  PROCEDURE Update_Data;
END;
/
 
CREATE OR REPLACE TRIGGER TR_CALCULARMORA_TMP
    AFTER UPDATE 
     ON Prestamo
     FOR EACH ROW
    DECLARE
        dias INTEGER;
        fechaDevOriginal DATE;
    BEGIN
         SELECT fechaDev INTO fechaDevOriginal FROM Prestamo WHERE idLector=:OLD.idLector AND idLibro=:OLD.idLibro;
         SELECT trunc(sysdate) - to_date(fechaDevOriginal, 'dd-mm-yyyy') INTO dias FROM dual;
         IF dias > 3 THEN
          Test_Package.TempTabla(:OLD.idLector).id_lib:=:OLD.idLibro;
          Test_Package.TempTabla(:OLD.idLector).Mora:=(dias-3)*5;
         END IF;
    END;
/
 
CREATE OR REPLACE TRIGGER TR_CALCULARMORA
After INSERT OR UPDATE OR DELETE ON Prestamo
BEGIN  IF test_Package.Latch THEN
    Test_Package.Update_Data;
  END IF;
END;
/
 

Yo escribo procedure Update_Data mas tarde (pordegracias, no tengo tiempo ahora)

[huesos52]

Por que BEFORE UPDATE?

Creo que para la necesidad de freddy31DA, es indiferente si se hace antes, en el instante o después de la ejecución de la sentencia.

Saludos

[AVBorland]

Buenos noches!

1. Datos inicialos:

Código SQL:

CREATE TABLE Prestamo(
 idLector NUMERIC(10) NOT NULL,
 idLibro NUMERIC(10) NOT NULL, 
 FechaPrestamo DATE NOT NULL,
 FechaDev DATE  NULL,
 FechaRealDev DATE NULL,
 Mora NUMBER(19,4) NULL,
 estado NUMBER(1) NULL
);
INSERT INTO Prestamo VALUES (1,1,to_date('09-06-2016','DD-MM-YYYY'),to_date('12-06-2016','DD-MM-YYYY'),'','',0);
INSERT INTO Prestamo VALUES (1,2,to_date('09-06-2016','DD-MM-YYYY'),to_date('12-06-2016','DD-MM-YYYY'),'','',0);
INSERT INTO Prestamo VALUES (1,3,to_date('09-06-2016','DD-MM-YYYY'),to_date('12-06-2016','DD-MM-YYYY'),'','',0);
 
SQL> SELECT * FROM prestamo;
 
  IDLECTOR    IDLIBRO FECHAPRES FECHADEV  FECHAREAL       MORA     ESTADO
---------- ---------- --------- --------- --------- ---------- ----------
         1          1 09-JUN-16 12-JUN-16                               0
         1          2 09-JUN-16 12-JUN-16                               0
         1          3 09-JUN-16 12-JUN-16                               0
 
SQL>
 

2. Realizo triggeros:

Código SQL:

CREATE OR REPLACE package Test_Package AS
  TYPE TTmpRec IS TABLE OF INTEGER INDEX BY Binary_integer;
  TYPE TTmpTabla IS TABLE OF TTmpRec INDEX BY binary_integer;
  
  TempTabla TTmpTabla;
  Latch BOOLEAN:=FALSE;
  PROCEDURE Update_Data;
END;
/
 
CREATE OR REPLACE package body Test_Package AS
 i INTEGER;
 j INTEGER;
 PROCEDURE Update_Data AS
  BEGIN
   latch := TRUE;
   IF test_Package.TempTabla.Count>0 THEN
     i:=test_Package.TempTabla.FIRST;
     while i IS NOT NULL loop
       j:=test_Package.TempTabla(i).FIRST;
       while j IS NOT NULL loop
         UPDATE Prestamo SET Mora=test_Package.TempTabla(i)(j) WHERE idLector=i AND idLibro=j;
         j:=test_Package.TempTabla(i).NEXT(j);
       END loop;
       i:=test_Package.TempTabla.NEXT(i);
     END loop;
   END IF;
   latch := FALSE;
  exception WHEN others THEN  
  latch := FALSE;
  raise;
   latch := FALSE;
  END;
END;
/
 
CREATE OR REPLACE TRIGGER TR_CALCULARMORA_TMP
    AFTER UPDATE 
     ON Prestamo
     FOR EACH ROW
    DECLARE
        dias INTEGER;
    BEGIN
         DIAS := TRUNC(SYSDATE) - :NEW.FECHADEV;
         IF dias > 3 THEN
          Test_Package.TempTabla(:OLD.idLector)(:OLD.idLibro):=(dias-3)*5;
         END IF;
    END;
/
 
CREATE OR REPLACE TRIGGER TR_CALCULARMORA
After INSERT OR UPDATE OR DELETE ON Prestamo
BEGIN  
  IF NOT test_Package.Latch THEN
    Test_Package.Update_Data;
  END IF;
END;
/
 
CREATE OR REPLACE TRIGGER Init_TEST
BEFORE INSERT OR UPDATE OR DELETE ON Prestamo
BEGIN
  IF NOT Test_Package.latch THEN
    Test_Package.TempTabla.DELETE();
  END IF;
END;
/
 

3. Resultado:

Código BASH:

SQL> select * from prestamo;
 
  IDLECTOR    IDLIBRO FECHAPRES FECHADEV  FECHAREAL       MORA     ESTADO
---------- ---------- --------- --------- --------- ---------- ----------
         1          1 09-JUN-16 12-JUN-16                               0
         1          2 09-JUN-16 12-JUN-16                               0
         1          3 09-JUN-16 12-JUN-16                               0
 
SQL> UPDATE Prestamo SET FECHADEV=to_date('10-06-2016','DD-MM-YYYY') WHERE idLector=1 ;
 
3 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from prestamo;
 
  IDLECTOR    IDLIBRO FECHAPRES FECHADEV  FECHAREAL       MORA     ESTADO
---------- ---------- --------- --------- --------- ---------- ----------
         1          1 09-JUN-16 10-JUN-16                   10          0
         1          2 09-JUN-16 10-JUN-16                   10          0
         1          3 09-JUN-16 10-JUN-16                   10          0
 
SQL> select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS') from dual;
 
TO_CHAR(SYSDATE,'DD
-------------------
15-06-2016 02:16:46
 
SQL>SQL> UPDATE Prestamo SET FECHADEV=to_date('11-06-2016','DD-MM-YYYY') WHERE idLeсtor=1;
 
3 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from prestamo;
 
  IDLECTOR    IDLIBRO FECHAPRES FECHADEV  FECHAREAL       MORA     ESTADO
---------- ---------- --------- --------- --------- ---------- ----------
         1          1 09-JUN-16 11-JUN-16                    5          0
         1          2 09-JUN-16 11-JUN-16                    5          0
         1          3 09-JUN-16 11-JUN-16                    5          0
 
SQL>