MySQL Lab Assignement #8
Turn in source code for trigger.
Write an DELETE trigger on order_line table similar to the INSERT trigger. The trigger should increase the units_on_hand in the part table for the part just deleted from the order_line table.
Write an UPDATE trigger on the order_line table. This one is confusing, so here are the rules:
1. If the new part_number is the same as the old part_number then update the units_on_hand in the parts table by the difference between the new number_ordered and the old number_ordered.
2. If the new and old part numbers are different, then increase the units on hand in the part table by the old number_ordered for the old part number. Then decrease the quantity on hand in part table for the new part number by the new number ordered
Bascially we are trying to keep the quantity on hand as accurate as possible. The best way to do this is with a trigger.