title: How to update a table AND throw an error in the same trigger without raising ORA-04091 mutating table error
body: <p>My homework requires me to create a single trigger which, under certain circumstances, updates a value AND raises an error. I am using an Oracle database which I access via the sqlplus command on a terminal.</p>
  
  <ul>
  <li>I cannot use « :NEW.attributeName := value » because raising the error prevents the update.</li>
  <li>I cannot use an update statement inside the trigger because it will raise a mutating table error.</li>
  <li>According to the problem statement I should be able to solve this within a single trigger and the problem statement is kind of vague on this but I think I am not allowed to use a procedure either.</li>
  <li>We have not seen temporary tables in class so they can't be used for the homework. We have however seen views.</li>
  </ul>
  
  <pre><code>CREATE OR REPLACE TRIGGER triggerName
  INSTEAD OF UPDATE OF attributeName ON TableName
  FOR EACH ROW
  BEGIN
      IF (:NEW.attributeName &lt; 0) THEN
          UPDATE  TableName
          SET attributeName = 0
          WHERE   attr0 = :NEW.attr0 AND
              attr1 = :NEW.attr1
          ;
          raise_application_error(-20101, 'You cannot update attributeName to a negative value.');
  END;
  /
  </code></pre>
  
  <p>I tried using a view as described in the following example (<a href="https://sgbd.developpez.com/oracle/ora-04091/#LI" rel="nofollow noreferrer">https://sgbd.developpez.com/oracle/ora-04091/#LI</a>). It's in French; it just says that creating a view should allow you to use an INSTEAD OF trigger, but I did exactly that and now the trigger is no longer triggered when I update the table.</p>
  
  <pre><code>CREATE TABLE CLIENT(
      IDC INTEGER PRIMARY KEY ,
      NOM VARCHAR2 (40));
  
  CREATE TABLE VOYAGE(
      IDV INTEGER PRIMARY KEY ,
      DESTINATION VARCHAR2 (40),
      MAXPLACE INTEGER ) -- nombre total de places     
  ;
  
  CREATE TABLE INSCRIPTION(
      IDC INTEGER REFERENCES CLIENT(IDC),
      IDV INTEGER REFERENCES VOYAGE(IDV),
      DATERESERV DATE ,
      CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
  
  INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
  INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
  INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
  COMMIT ;
  
  INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (10, 'VENISE', 25);
  INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (11, 'PRAGUE', 20);
  COMMIT ;
  
  -- Création d'une vue sur la table INSCRIPTION pour le support des déclencheurs INSTEAD OF
  CREATE OR REPLACE VIEW V_INSCRIPTION AS SELECT * FROM INSCRIPTION;
  
  CREATE OR REPLACE TRIGGER TRIG_V_INSCRIPTION INSTEAD OF INSERT ON V_INSCRIPTION FOR EACH ROW 
  DECLARE 
      NB_RESERVE INTEGER ; -- nombre de réservations déjà faites
      NB_MAXPLACE INTEGER ; -- nombre de places total
  
  BEGIN 
      SELECT COUNT (*) INTO NB_RESERVE FROM V_INSCRIPTION 
      WHERE IDC=:NEW.IDC
      AND IDV=:NEW.IDV;
      SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE 
      WHERE IDV=:NEW.IDV;
      IF NB_MAXPLACE - NB_RESERVE &lt; 1 THEN 
          DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet');
  
      ELSE 
          -- dans un déclencheur INSTEAD OF, l'instruction DML sous-jacente ne s'exécute pas. On traite donc l'insertion manuellement
          INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) VALUES (:NEW.IDC, :NEW.IDV, :NEW.DATERESERV);
      END IF ;
  END ;
  /
  
  -- DUGENOU aimerait bien aller à Venise :
  INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;
  1 ligne créée.
  </code></pre>
  
  <p>Is there any way for me to update the value of attributeName AND raise error ORA-20101 without using a procedure? Else, I'll assume I am allowed to.</p>