title: PL/SQL trigger cannot read table AFTER INSERT
body: <p>I am doing a small project, replicating a CVS Pharmacy DB. Learning about triggers in PL/SQL, I thought it would be a good idea to create a trigger that would update the store's inventory of a product when that product is purchased in the store (transaction). </p>
  
  <p>Now in order to do so, I have to do a AFTER INSERT on productLine_T (the table where a product in the transaction is being purchased with a quantity), store the transactionID, find the employeeID of newly inserted transaction, get storeID from employee of that transaction, which then allows me to get the corresponding inventory by storeID to update the productID in Inventory of that store by the orderedQuantity in productLine_T.</p>
  
  <p>After creating this trigger, I am getting the following error when I try inserting into productLine_T, described above:</p>
  
  <pre><code>Error starting at line 193 in command:
  INSERT INTO productLine_T(transactionID, productID, productQuantity, productFinalPrice)
  VALUES(00000006, 00000001, 2, 1.50)
  Error report:
  SQL Error: ORA-04091: table BSEWARDS.PRODUCTLINE_T is mutating, trigger/function may     not see it
  ORA-06512: at "BSEWARDS.ONPRODUCTPURCHASED", line 10
  ORA-04088: error during execution of trigger 'BSEWARDS.ONPRODUCTPURCHASED'
  04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
  *Cause:    A trigger (or a user defined plsql function that is referenced in
         this statement) attempted to look at (or modify) a table that was
         in the middle of being modified by the statement which fired it.
  *Action:   Rewrite the trigger (or function) so it does not read that table.
  </code></pre>
  
  <p>I don't understand why the trigger cannot read in the new value AFTER INSERT? I can include my ERD and any other visual representations needed for help when requested.</p>
  
  <p>*<em>Trigger: *</em></p>
  
  <pre><code>    create or replace 
  trigger onProductPurchased
    AFTER INSERT on productLine_T
    for each row
      declare
        transQty productLine_T.productQuantity%type;
        transID productLine_T.transactionID%type;
        prodID productLine_T.productID%type;
        empID Employee_T.employeeID%type;
        stID Store_T.storeID%type;
        --outOfStock exception;
    begin
      --store new transactionID in transID for query
      select transactionID, productID, productQuantity into transID, prodID, transQty
        FROM productLine_T
        WHERE productLine_T.transactionID = :new.transactionID;
      --find store by employee of transactionID to get inventory of store
  
        -- get employeeID of newly inserted transaction
        select emp.employeeID into empID FROM Employee_T emp, Transaction_T trans
          WHERE trans.transactionID = transID;
  
        -- get storeID from employee at store who made the transaction
        select st.storeID into stID FROM Store_T st, Employee_T emp
          WHERE emp.employeeID = empID;
  
        --get inventory by storeID and change inventoryQty based on productID quantity
        update Inventory_T 
          set inventoryQuantity = inventoryQuantity - :new.productQuantity
          WHERE Inventory_T.storeID = stID AND Inventory_T.storeID = prodID;
    end;
  </code></pre>