Oracle SQL Creating Advanced Trigger Statement

Vote count:

0

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 with these 2 tables I would like to have a trigger that goes off if the Inventory.Quantity 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, Date

• 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'


• Date will be a random date in 2010 after February.

Any advice on how to create a trigger to do this would be greatly appreciated. Thanks!

asked 1 min ago

Blued00d

35

Oracle SQL Creating Advanced Trigger Statement