problem with trigger in oracle

Question

the problem is this : I implemented a trigger on the table called CLAN_AFFILIATI that increases (if inseriemento) and decreases (in case of cancellation) an attribute (NUMAFFILIATI) of another table called CLAN. what I would do is block the update NUMAFFILIATI of Clan by the user and had thought to r another trigge on CLAN that did this:

trigger on 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;

trigger on 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;

but so I'm doing is reporting an 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

how can I solve this problem ....

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 — kafka
– kafka, Commented Aug 31, 2010 at 13:22

Martin Mares · Accepted Answer · 2010-08-31 15:43:54Z

2

This is propably solution:

I tested it with this sample tables:

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

You need this helper package.

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;
/

Trigger on CLAN table:

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;
/

Before statement trigger on CLAN_AFFILIATI table:

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;
/

After statement trigger on CLAN_AFFILIATI table:

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;
/

Row Insert/Delete trigger on CLAN_AFFILIATI table:

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;
/

Now working this (without 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');

edited Aug 31, 2010 at 15:43

answered Aug 31, 2010 at 14:40

Martin Mares

3801 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

kafka Over a year ago

thanks for your answer you have been very kind, almost everything works perfectly, the only problem is the cancellation, ie NUMAFFILIATI is not updated if a row is deleted from CLAN_AFFILIATI

Martin Mares Over a year ago

FIRST - is raised trigger "TRG_CLAN_AFFILIATI_BEFORE_STMT" - Oracle raise it before any data manipulation in the table. This is place, where you must clear array(STORE_NOMES.g_array_nomes) - otherwise it will not work. SECOND - is raised Trigger "AGGIORNAMENTONUMAFFILIATI", there is a place, where stored NOMES in array(STORE_NOMES.g_array_nomes) and its operations(INSERT or DELETE). THIRD (and last) is raised after statement trigger "TRG_CLAN_AFFILIATI_AFTER_STMT", which Oracle rised after everything DML(data-manupilation) is done. This is the place, where you can call update on CLAN table.

Martin Mares Over a year ago

Oracle TYPE STORE_NOMES.g_array_nomes is similar to MEMORY (remembers what names and what operations were performed).

kafka Over a year ago

But I noticed a problem when a row in the table is cleared CLAN_AFFILIATI NUMAFFILIATI is not updated.

Martin Mares Over a year ago

Sorry, I was wondering query :) I look at it. And I will let you know... Give me 30 minutes...

|

Erich Kitzmueller · Accepted Answer · 2010-08-31 13:35:01Z

0

Change the first trigger "AggiornamentoNumAffiliati" so that it doesn't immediately try to update clan, but stores the name (NOME) in a PL/SQL-Table within a Package; then, you make an AFTER INSERT OR DELETE (but without the FOR EACH ROW clause) trigger that reads the PL/SQL table from the package and updates the CLANs accordingly.

answered Aug 31, 2010 at 13:35

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

2 Comments

kafka Over a year ago

how to create a PL/SQL-table?

Erich Kitzmueller Over a year ago

see martin's answer, he was more diligent than I

Vladaimir Cetkovic · Accepted Answer · 2010-08-31 13:36:24Z

0

I don't have my developer tools with me, but it looks to me as though your getting yourself into a cyclic dependency issue of sorts. When your CLAN_AFFILIATI trigger is raised, in it you do an update of CLAN which calls the second trigger, which has a select from the CLAN_AFFILIATI table in the ELSE block.

Maybe the before insert (first query), and after insert(second query) have an affect also.

answered Aug 31, 2010 at 13:36

Vladaimir Cetkovic

835 bronze badges

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2010-09-02 11:22:41Z

0

ORA-04091 is also known as a "mutating table" error - basically, row triggers cannot query or alter the table on which the trigger operates.

@Martin's answer is the classic description of how to work around this issue, but it you're on Oracle 11+ you can use a compound trigger to do the same thing.

Share and enjoy.

answered Sep 2, 2010 at 11:22

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

Collectives™ on Stack Overflow

problem with trigger in oracle

4 Answers 4

8 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

8 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related