Parker's INFO 4407 – Database Design & Implementation

Deliverable VII – Triggers

Trigger #1:

  • The first trigger represents a typical inventory management task. Whenever an item is sold the associated inventory count is decremented. The system should automatically check to see if the inventory count has fallen below the reorder quantity. Write a trigger to compare the Product_Category (or Model) attribute modelInventoryCount to the modelReorderQuantity. If the value is less than or equal to the reorder quantity then the reorderNecessary attribute should be set to true. The trigger should be invoked whenever the modelInventoryCount or modelReorderQuantity attributes are updated.

Trigger #2:

  • The next trigger calls the credit card validation stored procedure or stored function. When an item is purchased by credit card, the card number must be validated. Write a trigger to execute the stored procedure or function that you developed to validate the card number when the value of the purchaseAgreementCardNumberUsed field is inserted or updated.

Note: To get a list of triggers in MySQL use the statement below:

SELECT * FROM information_schema.TRIGGERS;

Hint: You can generate an error message from a trigger using the signal statement :

SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid Credit Card Number';

It appears that MySQL treats triggers like queries, so you may want to save them as script files.