Consider the following relational database: Product (ID, Name, StockQuantity) Cu

Question

Consider the following relational database: Product (ID, Name, StockQuantity) Customer (ID, Name, TotalPuchases) Sale (ID, SaleDate, #IdCustomer, TotalAmount) SaleDetails #ldSale #ldProduct, QuantitySold, Price) Using the relational database above, write the create statements of the required triggers in order to maintain updated values of the following fields for every INSERT, UPDATE, and DELETE operation that can occur in the database: Stock Quantity Total Puchases TotalAmount Submit one (1) MySQL Workbench MWB file with your full ID and name in the filename.

Explanation / Answer

1. Defination of INSERT trigger:

DELIMITER //
DROP TRIGGER IF EXISTS my_insert_trigger//
CREATE DEFINER=root@localhost TRIGGER my_insert_trigger
    AFTER INSERT ON `Product`
    FOR EACH ROW

BEGIN
    -- Calling the procedure if there is an INSERT on `Product`

    CALL procedure_to_run_processes_due_to_changes_on_Product();
END//
DELIMITER ;

2. Defination of UPDATE trigger

DELIMITER //
DROP TRIGGER IF EXISTS my_update_trigger//

CREATE DEFINER=root@localhost TRIGGER my_update_trigger
    AFTER UPDATE ON `Product`
    FOR EACH ROW
BEGIN
    -- Calling the procedure if there is an UPDATE on `Product`
    CALL procedure_to_run_processes_due_to_changes_on_Product();
END//
DELIMITER ;

3. Defination of DELETE trigger

DELIMITER //
DROP TRIGGER IF EXISTS my_delete_trigger//

CREATE DEFINER=root@localhost TRIGGER my_delete_trigger
    AFTER DELETE ON `Product`
    FOR EACH ROW
BEGIN
    -- Calling the procedure if there is DELETE on `Product`
    CALL procedure_to_run_processes_due_to_changes_on_Product();
END//
DELIMITER ;

Similarly above triggers created for remaing two tables Customer,Sale.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---