title: PL/SQL Trigger Mutating Table on compound trigger
body: <p>writing a trigger on table that updates another table on oracle 
  trigger throws mutating table error.
  I know the error is coming because i am updating the table that is reference in my row level block but i cannot figure out a work around</p>
  
  <p>EDIT: This code throws a PL/SQL: numeric or value error: NULL index table key value but i seem to have gotten around the mutating table.</p>
  
  <pre><code>create or replace TRIGGER DSPLATE_WELL_VOLUME_V3
  FOR UPDATE ON DSPLATE 
  COMPOUND TRIGGER
  
  
  TYPE t_PLATE_ID IS TABLE OF DSPLATE.PLATE_ID%TYPE;
  v_PLATE_ID  t_PLATE_ID;
  TYPE t_NEW_AMOUNT IS TABLE OF DSPLATE.AMOUNT%TYPE;
  v_NEW_AMOUNT  t_NEW_AMOUNT;
  
  
  BEFORE STATEMENT IS
  BEGIN
      v_PLATE_ID := t_PLATE_ID();
      v_NEW_AMOUNT := t_NEW_AMOUNT();
  END BEFORE STATEMENT;
  
  
  BEFORE EACH ROW IS
  BEGIN
    IF :NEW.PLATE_TYPE != :OLD.PLATE_TYPE AND :NEW.PLATE_TYPE = 'Assay Plate' 
        AND :NEW.AMOUNT_INITIAL IS NOT NULL AND :OLD.AMOUNT_INITIAL IS NULL THEN
             v_PLATE_ID(v_PLATE_ID.LAST) := :OLD.PLATE_ID;
             v_NEW_AMOUNT(v_NEW_AMOUNT.LAST) := :NEW.AMOUNT_INITIAL;
    END IF;
  END BEFORE EACH ROW;
  
  AFTER STATEMENT IS
  BEGIN
      FOR p IN 1..v_PLATE_ID.LAST LOOP
                    UPDATE DSPLATE_WELL
              SET AMOUNT = AMOUNT - v_NEW_AMOUNT(p)
                WHERE WELL_ID IN (SELECT DSPLATE_WELL.WELL_ID
                FROM DSPLATE INNER JOIN DSPLATE_WELL ON DSPLATE_WELL.PLATE_ID = DSPLATE.PLATE_ID
                WHERE DSPLATE_WELL.WELL_VALUE IN (SELECT DSPLATE_WELL.WELL_VALUE
                FROM DSPLATE_WELL INNER JOIN DSPLATE ON DSPLATE.PLATE_ID = DSPLATE_WELL.PLATE_ID WHERE DSPLATE.PLATE_ID = v_PLATE_ID(p))
                AND DSPLATE.PLATE_TYPE = 'Cherry Pick Plate' AND DSPLATE.LOCATION_ID = 1420);
          END LOOP;
  END AFTER STATEMENT;
  
  
  END;
  </code></pre>
  
  <p>The original trigger i wrote that worked is this but I want it to be written as a compound trigger instead, when a plate_type is set to 'assay plate' the query looks up all well_values of that plate_id on table DSPLATE_WELL, the well_values correspond back to many different plate_ids on table DSPLATE with plate_type 'cherry pick' and specific WELL_IDs where the WELL_VALUES originally came from must have their volume amount updated. </p>
  
  <pre><code>create or replace TRIGGER INSERT_DSPLATE_WELL_VOLUME 
  AFTER UPDATE ON DSPLATE  
  FOR EACH ROW
  DECLARE
  pragma autonomous_transaction;
  
  
  BEGIN
    IF :NEW.PLATE_TYPE != :OLD.PLATE_TYPE AND :NEW.PLATE_TYPE = 'Assay Plate' 
  AND :NEW.AMOUNT_INITIAL IS NOT NULL AND :OLD.AMOUNT_INITIAL IS NULL
    THEN
        UPDATE DSPLATE_WELL
          SET AMOUNT = AMOUNT - :NEW.AMOUNT_INITIAL
            WHERE WELL_ID IN (SELECT DSPLATE_WELL.WELL_ID
            FROM DSPLATE INNER JOIN DSPLATE_WELL ON DSPLATE_WELL.PLATE_ID = DSPLATE.PLATE_ID
            WHERE DSPLATE_WELL.WELL_VALUE IN (SELECT DSPLATE_WELL.WELL_VALUE
            FROM DSPLATE_WELL INNER JOIN DSPLATE ON DSPLATE.PLATE_ID = DSPLATE_WELL.PLATE_ID WHERE DSPLATE.PLATE_ID = :OLD.PLATE_ID)
            AND DSPLATE.PLATE_TYPE = 'Cherry Pick Plate' AND DSPLATE.LOCATION_ID = 1420);
            COMMIT;
    END IF;
  END;
  </code></pre>