title: Oracle SQL Trigger: After Update, Insert Rows into Another Table
body: <p>I am trying to write a trigger on one table that is triggered when the quantity in stock = 0. It will then add a row to another table with the product_id and store_id to indicate that item needs to be evaluated for reorder. After over 4 hours of searching and trying, I made an account here. I'm a new user and this could be totally off.</p>
  
  <p>This is my latest attempt:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER ORDERS_AFTER_UPDATE
  AFTER UPDATE 
  OF QUANTITY_STOCK 
  ON INVENTORY 
  FOR EACH ROW
  WHEN (NEW.QUANTITY_STOCK - OLD.QUANTITY_STOCK = 0)
  BEGIN
  SELECT PRODUCT_ID, STORE_ID
  INTO PURCHASE_NEW
  FROM INVENTORY
  WHERE QUANTITY_STOCK = 0;
  END;
  </code></pre>
  
  <p>The relevant tables are INVENTORY, having PRODUCT_ID, STORE_ID, and QUANTITY_STOCK and PURCHASE_NEW, having PRODUCT_ID and STORE_ID. I want it to copy PRODUCT_ID and STORE_ID into the PURCHASE_NEW table from the INVENTORY table when the value of QUANTITY_STOCK = 0.</p>
  
  <p>Errors I'm receiving are: </p>
  
  <pre><code>Error(8,1): PL/SQL: SQL Statement ignored
  Error(9,6): PLS-00403: expression 'PURCHASE_NEW' cannot be used as an INTO-target of a SELECT/FETCH statement
  Error(9,19): PL/SQL: ORA-00904: : invalid identifier
  </code></pre>
  
  <p>Thank you very much.</p>