Oracle SQL Updating Trigger Statement

Hey all so let me see if I can clearly explain what I would like this trigger to exactly do.

First of all the trigger will deal with 2 tables:

Delivery Table:

DeliveryNum |ItemName | Quantity | Department |  Date
------------|---------|----------|------------|--------
       10001|   Jersey|       200|    clothing|02/06/10
       10002| Baseball|       999|  recreation|02/15/10
       10003|      Cap|       250|    clothing|02/15/10

Inventory Table:

ItemName | Quantity
---------|---------
   Jersey|      650
 Baseball|     2555
      Cap|      400
      Bat|      175
   Cleats|      225
        etc......

Now I would like to have a trigger that is triggered if the Inventory.Quantity of an item is ever less than 50.

What I would like to happen if this requirement is met: A new delivery is made in the delivery table of the Item that has fallen below sufficient quantity.

This new row that is being added to the Delivery table must include the: DeliveryNum, ItemName, Quantity, Department

• DeliveryNum must be the next proper ordered number in the column.


• ItemName must match the ItemName that has its Quantity in Inventory table < 50.


• Quantity must be a random number between 100 - 200


• Department will just simply be 'sales'

Now I have started to create my trigger however I have hit a wall. Here is what I have so far:

CREATE OR REPLACE TRIGGER QuantityTrigger
BEFORE
UPDATE ON QITEM FOR EACH ROW
BEGIN
 IF :new.QtyOnHand < 100 THEN
    INSERT INTO QDEL
    (DELNO, DELQTY, ITEMNAME, DEPTNAME, SPLNO)
    VALUES
    (seq_delno.nextval, dbms_random.value(100,200) , 'HOW DO I GET CORRECT ITEM HERE?', 'sales', 104);
 END IF;
END;

GOALS:

• I have created a sequence for taking care of the delivery number issue.


• I have generated a random number between (100-200) to be the delivery quantity.


• I have NOT figured out how to display the same ItemName here that caused the trigger.

How can this be achieved? What is the proper way to handle a situation like this. Any advice on how I can improve my trigger to do achieve my goals would be greatly appreciated. Thanks!