sql - solucion - problema con el gatillo en el oráculo

trigger pl sql

Question

el problema es este: Implementé un disparador en la tabla llamado CLAN_AFFILIATI que aumenta (si hay inseriemento) y disminuye (en caso de cancelación) un atributo (NUMAFFILIATI) de otra tabla llamada CLAN. lo que haría sería bloquear la actualización NUMAFFILIATI de Clan por el usuario y había pensado en otra activación en CLAN que hiciera esto:

desencadenar en CLAN_AFFILIATI (CLAN VARCHAR, AFFILIATO VARCHAR, RUOLO VARCHAR)

CREATE OR REPLACE TRIGGER  "AggiornamentoNumAffiliati" 
AFTER INSERT OR DELETE ON CLAN_AFFILIATI
FOR EACH ROW
DECLARE
  CLAN_APPARTENENZA VARCHAR(20);
BEGIN


  IF INSERTING THEN

    SELECT NOME INTO CLAN_APPARTENENZA
    FROM CLAN
    WHERE NOME=:new.CLAN;

    UPDATE CLAN 
    SET NUMAFFILIATI=NUMAFFILIATI+1
    WHERE CLAN_APPARTENENZA=NOME;
  ELSE
    SELECT NOME INTO CLAN_APPARTENENZA
    FROM CLAN
    WHERE NOME=:old.CLAN;

    UPDATE CLAN 
    SET NUMAFFILIATI=NUMAFFILIATI-1
    WHERE CLAN_APPARTENENZA=NOME;
  END IF; 
END;

desencadenar en CLAN (NAME VARCHAR, NUMAFFILIATI INTEGER)

CREATE OR REPLACE TRIGGER  "ModificaNumAffiliati" 
BEFORE INSERT OR UPDATE OF NUMAFFILIATI ON CLAN
FOR EACH ROW
DECLARE
CONT NUMBER:=0;  
BEGIN

   IF INSERTING THEN
      IF :new.NUMAFFILIATI <> 0 THEN
           RAISE_APPLICATION_ERROR(-20016,''NUMERO ERRATO'');
      END IF;
   ELSE
      SELECT COUNT(*) INTO CONT
      FROM CLAN_AFFILIATI
      WHERE :old.NOME=CLAN;
      IF CONT <> :new.NUMAFFILIATI THEN
          RAISE_APPLICATION_ERROR(-20017,''NUMERO ERRATO'');
      END IF;
   END IF;
 END;

pero lo que estoy haciendo es informar un error:

error ORA-04091: Table ANTONIO.CLAN_AFFILIATI is being modified, the trigger / function can not read 
ORA-06512: at "ANTONIO.ModificaNumAffiliati", line 10 
ORA-04088: error during execution of trigger ''ANTONIO.ModificaNumAffiliati'' 
ORA-06512: at "ANTONIO.AggiornamentoNumAffiliati", line 12 
ORA-04088: error during execution of trigger ''ANTONIO.AggiornamentoNumAffiliati

Como puedó resolver esté problema ....

Answer 1

Cambie el primer activador "AggiornamentoNumAffiliati" para que no intente actualizar el clan inmediatamente, pero almacene el nombre (NOME) en una tabla PL / SQL dentro de un paquete; luego, crea un desencadenante DESPUÉS DE INSERTAR O ELIMINAR (pero sin la cláusula FOR E HUN CLOT) que lee la tabla PL / SQL del paquete y actualiza los CLAN en consecuencia.

Answer 2

Esta es una solución viable:

Lo probé con estas tablas de muestra:

CREATE TABLE CLAN_AFFILIATI(CLAN VARCHAR2(100),AFFILIATO VARCHAR2(100),RUOLO VARCHAR2(100));
CREATE TABLE CLAN (NOME VARCHAR2(100) ,NUMAFFILIATI NUMBER(10));

Necesitas este paquete de ayuda.

CREATE OR REPLACE PACKAGE STORE_NOMES
AS

    TYPE record_nomes IS RECORD (
        nome VARCHAR2(100),
        operation VARCHAR2(100) -- insert or delete
    );

    TYPE array_type_nomes IS TABLE OF record_nomes INDEX BY BINARY_INTEGER;
    g_array_nomes array_type_nomes;

END STORE_NOMES;
/

Disparador en la tabla CLAN:

CREATE OR REPLACE TRIGGER MODIFICANUMAFFILIATI
  BEFORE INSERT OR UPDATE OF NUMAFFILIATI ON CLAN
FOR EACH ROW
DECLARE
    l_CONT NUMBER:=0;
BEGIN

   IF INSERTING THEN
      -- prevent inserting <> 0
      IF :new.NUMAFFILIATI <> 0 THEN
           RAISE_APPLICATION_ERROR(-20016,''NUMERO ERRATO'');
      END IF;
   ELSE
      SELECT COUNT(*) INTO l_CONT
      FROM CLAN_AFFILIATI
      WHERE CLAN = :old.NOME;
      IF l_CONT <> :new.NUMAFFILIATI THEN
          RAISE_APPLICATION_ERROR(-20017,''NUMERO ERRATO'');
      END IF;
   END IF;
 END;
/

Antes del desencadenante de declaración en la tabla CLAN_AFFILIATI:

CREATE OR REPLACE TRIGGER TRG_CLAN_AFFILIATI_BEFORE_STMT
  BEFORE INSERT OR DELETE
ON CLAN_AFFILIATI
DECLARE
BEGIN
    STORE_NOMES.g_array_nomes.DELETE;
END;
/

Después del desencadenante de declaración en la tabla CLAN_AFFILIATI:

CREATE OR REPLACE TRIGGER TRG_CLAN_AFFILIATI_AFTER_STMT
  AFTER INSERT OR DELETE
ON CLAN_AFFILIATI
DECLARE
BEGIN
    FOR i IN STORE_NOMES.g_array_nomes.FIRST..STORE_NOMES.g_array_nomes.LAST LOOP
        IF(STORE_NOMES.g_array_nomes(i).operation = ''INSERTING'') THEN
            UPDATE CLAN
            SET NUMAFFILIATI=NUMAFFILIATI+1
            WHERE NOME = STORE_NOMES.g_array_nomes(i).NOME;
        ELSIF(STORE_NOMES.g_array_nomes(i).operation = ''DELETING'') THEN
            UPDATE CLAN
            SET NUMAFFILIATI=NUMAFFILIATI-1
            WHERE NOME = STORE_NOMES.g_array_nomes(i).NOME;
        END IF;
    END LOOP;
END;
/

Insertar fila / Eliminar activador en la tabla CLAN_AFFILIATI:

CREATE OR REPLACE TRIGGER AGGIORNAMENTONUMAFFILIATI
  BEFORE INSERT OR DELETE ON CLAN_AFFILIATI
FOR EACH ROW
DECLARE
  l_CLAN_APPARTENENZA VARCHAR(20);
BEGIN

  IF INSERTING THEN

    SELECT NOME INTO l_CLAN_APPARTENENZA
    FROM CLAN
    WHERE NOME = :new.CLAN;

    STORE_NOMES.g_array_nomes(STORE_NOMES.g_array_nomes.COUNT).nome := :new.CLAN;
    STORE_NOMES.g_array_nomes(STORE_NOMES.g_array_nomes.LAST).operation := ''INSERTING'';

  ELSE
    SELECT NOME INTO l_CLAN_APPARTENENZA
    FROM CLAN
    WHERE NOME = :old.CLAN;

    STORE_NOMES.g_array_nomes(STORE_NOMES.g_array_nomes.COUNT).nome := :old.CLAN;
    STORE_NOMES.g_array_nomes(STORE_NOMES.g_array_nomes.LAST).operation := ''DELETING'';
  END IF;
END;
/

Ahora trabajando esto (sin ORACLE-EXCEPTION):

INSERT INTO CLAN(NOME, NUMAFFILIATI) VALUES(''Antonio'', 0);
INSERT INTO CLAN_AFFILIATI(CLAN,AFFILIATO,RUOLO) values(''Antonio'',''Affiliato1'',''Ruolo1'');
INSERT INTO CLAN_AFFILIATI(CLAN,AFFILIATO,RUOLO) values(''Antonio'',''Affiliato2'',''Ruolo2'');

Answer 3

No tengo mis herramientas de desarrollador conmigo, pero me parece como si te metieras en una especie de dependencia cíclica. Cuando se activa su activador CLAN_AFFILIATI, en él realiza una actualización de CLAN que llama al segundo activador, que tiene una selección de la tabla CLAN_AFFILIATI en el bloque ELSE.

Tal vez la inserción anterior (primera consulta) y luego de la inserción (segunda consulta) también tengan un efecto.

Answer 4

ORA-04091 también se conoce como un error de "tabla de mutación": básicamente, los activadores de fila no pueden consultar o alterar la tabla en la que opera el activador.

La respuesta de @Martin es la descripción clásica de cómo solucionar este problema, pero si usa Oracle 11+ puede usar un activador compuesto para hacer lo mismo.

Comparte y Disfruta.