trigger and mutant table

Question

trigger and mutant table

Navigation

#1 by (1 votes)

1

I am having problems updating the rows of a table due to a necessary trigger that I have. The error is that of the mutant tables and I do not know how to modify my trigger so that it does not happen.

The structure of the table is as follows:

CREATE TABLE CITAS (
    OID_CITA NUMBER PRIMARY KEY, 
    HORA VARCHAR2(5) NOT NULL, 
    FECHA DATE NOT NULL, 
    OID_CLINICA NUMBER, 
    USUARIO_MEDICO VARCHAR2(75) NOT NULL, 
    USUARIO_RECEPCIONISTA VARCHAR2(75), 
    DNI_PACIENTE CHAR(9), 
    FOREIGN KEY(OID_CLINICA) REFERENCES CLINICAS(OID_CLINICA), 
    FOREIGN KEY(USUARIO_MEDICO) REFERENCES MEDICOS(USUARIO), 
    FOREIGN KEY(USUARIO_RECEPCIONISTA) 
    REFERENCES RECEPCIONISTAS(USUARIO), 
    FOREIGN KEY(DNI_PACIENTE) REFERENCES PACIENTES(DNI)
);

The error

An error occurred while saving the changes in the "JHO" table. "QUOTES": Row 231: ORA-04091: table JHO.CITAS is mutating, trigger / function may not see it ORA-06512: at " JHO.TR_CITA_IDENTICA ", line 7 ORA-04088: error during execution of trigger 'JHO.TR_CITA_IDENTICA'

The trigger:

CREATE OR REPLACE TRIGGER TR_CITA_IDENTICA
BEFORE INSERT OR UPDATE OF USUARIO_MEDICO,FECHA,HORA ON CITAS
FOR EACH ROW
DECLARE
  V_MEDICO VARCHAR(40) := :NEW.USUARIO_MEDICO;
  V_FECHA DATE := :NEW.FECHA;
  V_HORA VARCHAR2(20) := :NEW.HORA;
  V_CUENTA NUMBER;
BEGIN
  SELECT COUNT(*) INTO V_CUENTA FROM CITAS WHERE USUARIO_MEDICO = V_MEDICO 
AND FECHA = V_FECHA AND HORA = V_HORA;
  IF V_CUENTA > 0 THEN
    RAISE_APPLICATION_ERROR(-20004,'No puede haber más de una cita de un 
mismo médico para una fecha y una hora específica');
  END IF;
END;
/

sql oracle

asked by Ray 05.08.2017 в 03:24

source

1 answer

Error trying to install a package with Nuget in Visual Studio 2017 Constraint null and dni

score 1 · Accepted Answer

The following error appears because the trigger is executing a query to the same table that fired it.

An error occurred while saving the changes in the "JHO" table. "QUOTES": Row 231: ORA-04091: table JHO.CITAS is mutating, trigger / function may not see it ORA-06512: at " JHO.TR_CITA_IDENTICA ", line 7 ORA-04088: error during execution of trigger 'JHO.TR_CITA_IDENTICA'

SOLUTION 1 - USE AUTONOMOUS TRANSACTIONS

The autonomous transactions are transactions that although they are called within another transaction, their actions are independent of the main transaction.

We check how the table is currently:

select * from citas;

  OID_CITA HORA  FECHA       OID_CLINICA USUARIO_MEDICO   USUARIO_RECEPCIONISTA   DNI_PACIENTE
---------- ----- ----------- ----------- --------------   ---------------------   ------------
         1 07:35 05/08/2017            1 1                1                       1234
         2 07:50 05/08/2017            1 1                1                       1234
         3 08:00 05/08/2017            1 1                1                       1222

We created the trigger.

CREATE OR REPLACE TRIGGER TR_CITA_IDENTICA
BEFORE INSERT OR UPDATE OF USUARIO_MEDICO,FECHA,HORA ON CITAS
FOR EACH ROW

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;
  V_MEDICO VARCHAR(40) := :NEW.USUARIO_MEDICO;
  V_FECHA DATE := :NEW.FECHA;
  V_HORA VARCHAR2(20) := :NEW.HORA;
  V_CUENTA NUMBER;
BEGIN

  SELECT COUNT(*) INTO V_CUENTA FROM CITAS WHERE USUARIO_MEDICO = V_MEDICO 
    AND FECHA = V_FECHA AND HORA = V_HORA;

  IF V_CUENTA > 0 THEN
    RAISE_APPLICATION_ERROR(-20004,'No puede haber más de una cita de un 
    mismo médico para una fecha y una hora específica');
  END IF;
END;
/

We launched the UPDATE:

SQL> update citas set hora = '07:36' where oid_cita = 1;

1 row updated

We launch another UPDATE that collides with the time of another record:

SQL> update citas set hora = '07:50' where oid_cita = 1;

Now we see the following error that is what we want to come out:

update appointments set time = '07: 50 'where oid_cita = 1 ORA-20004: There can not be more than one appointment of one same doctor for a specific date and time ORA-06512: in "JHO.TR_CITA_IDENTICA", line 14 ORA-04088: error during the execution of the trigger 'JHO.TR_CITA_IDENTICA'

This solution is only effective when you update one record at a time because if you use some way of updating several records at the same time, you may not validate.

SOLUTION 2 - VALIDATE FROM THE APPLICATION

If you are working at the level of an application it is advisable in this case to do the validation from the program, so you avoid consulting the same table from the trigger.