title: Updating Stock Information When a Product Request Is Filled
body: <p>Problem:</p>
  
  <p>Brewbean’s has a BB_PRODUCT_REQUEST table where requests to refill stock levels are inserted automatically via a trigger. After the stock level falls below the reorder level, this trigger fires and enters a request in the table. This procedure works great; however, when store clerks record that the product request has been filled by updating the table’s DTRECD and COST columns, they want the stock level in the product table to be updated. Create a trigger named BB_REQFILL_TRG to handle this task, using the following steps as a guideline: 
  1. In SQL Developer, run the following INSERT statement to create a product request you can use in this assignment:</p>
  
  <pre><code>INSERT INTO bb_product_request (idRequest, idProduct, dtRequest, qty) VALUES (3, 5, SYSDATE, 45); COMMIT;
  </code></pre>
  
  <ol start="2">
  <li>Create the trigger (BB_REQFILL_TRG) so that it fires when a received date is entered in the BB_PRODUCT_REQUEST table. This trigger needs to modify the STOCK column in the BB_PRODUCT table to reflect the increased inventory. 3. Now test the trigger. First, query the stock and reorder data for product 5. Once the received date is updated, the value of the old stock becomes the new stock of 86. I'm not sure what I am doing wrong with the trigger. </li>
  </ol>
  
  <p>Code:</p>
  
  <pre><code>    CREATE OR REPLACE TRIGGER BB_REQFILL_TRG 
  AFTER UPDATE OF DTRECD ON BB_PRODUCT_REQUEST
  for EACH ROW
  
  declare
      cursor c_product is
          select stock, reorder
          from bb_product
          where idproduct = :OLD.idproduct;
  
      v_dtrecd DATE := sysdate;
  
  --    cursor c_dtrecd is
  
  BEGIN
      select dtrecd
          into v_dtrecd
      from bb_product_request
      where idrequest = :OLD.idrequest;
  
      for x in c_product loop
          if v_dtrecd = :new.dtrecd then
              update bb_product
              set stock = x.stock + x.reorder
              where idproduct = :old.idproduct;
          end if;
      end loop;
  END;
      update bb_product_request -- new value when updated (86) from the trigger    set DTRECD = sysdate, cost = 225
      where idrequest = 3;
  
      select stock, reorder -- stock (41), reorder (45)
      from bb_product
      where idproduct = 5;
  
      select * -- this verifies that dtrecd is updated as well
      from bb_product_request
      where idproduct = 5;
  </code></pre>