title: problem with trigger in oracle
body: <p>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:</p>
  
  <p>trigger on  CLAN_AFFILIATI(CLAN VARCHAR,AFFILIATO VARCHAR,RUOLO VARCHAR) </p>
  
  <pre><code>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;
  </code></pre>
  
  <p>trigger on CLAN (NAME VARCHAR ,NUMAFFILIATI INTEGER)</p>
  
  <pre><code>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 &lt;&gt; 0 THEN
             RAISE_APPLICATION_ERROR(-20016,'NUMERO ERRATO');
        END IF;
     ELSE
        SELECT COUNT(*) INTO CONT
        FROM CLAN_AFFILIATI
        WHERE :old.NOME=CLAN;
        IF CONT &lt;&gt; :new.NUMAFFILIATI THEN
            RAISE_APPLICATION_ERROR(-20017,'NUMERO ERRATO');
        END IF;
     END IF;
   END;
  </code></pre>
  
  <p>but so I'm doing is reporting an error: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>how can I solve this problem ....</p>