Unguided Exercises - Triggers - Usage of triggers in MySQL using the classicmodels database

[akash-coded]

Certainly! Triggers in MySQL can be extremely useful for automating actions that should take place when data is modified. Below are some advanced exercises based on the classicmodels database, intended to challenge experienced data engineers on the myriad usages of triggers.

---

Exercise 1: Audit Table for customers

Context and Problem Statement:

Create an AFTER UPDATE trigger on the customers table that logs changes to a separate CustomerAudit table. The audit table should capture who made the change and what data was altered.

Trigger Type:

• AFTER UPDATE

Columns to Log:

• customerName
• phone
• salesRepEmployeeNumber

---

Exercise 2: Inventory Control for products

Context and Problem Statement:

Create an AFTER INSERT trigger on the orderdetails table to update a separate Inventory table. If the quantity in the inventory falls below a minimum threshold, mark the product as needing restock.

Trigger Type:

• AFTER INSERT

Actions:

• Decrease the inventory
• Flag for restock if below threshold

---

Exercise 3: Automatic Discounts on orders

Context and Problem Statement:

For orders above a certain total value, an automatic discount should be applied. Create an AFTER INSERT trigger on the orders table that modifies the order to include a discount if the total amount is above $10,000.

Trigger Type:

• AFTER INSERT

Actions:

• Calculate total order amount
• Apply discount if above $10,000

---

Exercise 4: Employee Performance Metrics

Context and Problem Statement:

When an employee makes a sale (an entry in the orders table), an AFTER INSERT trigger should update the employee's performance metrics in an EmployeeMetrics table.

Trigger Type:

• AFTER INSERT

Actions:

• Update the sales counter for the employee
• Calculate the average sales value for the employee

---

Exercise 5: Cascade Delete for products and orderdetails

Context and Problem Statement:

When a product is deleted from the products table, ensure that all related entries in the orderdetails table are also removed to maintain data integrity.

Trigger Type:

• BEFORE DELETE

Actions:

• Check for related entries in orderdetails
• Delete those entries

---

These exercises cover various real-world scenarios where triggers can come in handy to automate processes and maintain data integrity.

---

---

[Gayathiriramalingam2024]

DELIMITER //
CREATE TRIGGER after_customer_update
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
IF OLD.customerName != NEW.customerName OR OLD.phone != NEW.phone OR OLD.salesRepEmployeeNumber != NEW.salesRepEmployeeNumber THEN
INSERT INTO CustomerAudit (customer_id, changed_by, old_customerName, new_customerName, old_phone, new_phone, old_salesRepEmployeeNumber, new_salesRepEmployeeNumber)
VALUES (OLD.customerNumber, CURRENT_USER(), OLD.customerName, NEW.customerName, OLD.phone, NEW.phone, OLD.salesRepEmployeeNumber, NEW.salesRepEmployeeNumber);
END IF;
END;
//
DELIMITER ;

DELIMITER //
CREATE TRIGGER after_orderdetails_insert
AFTER INSERT ON orderdetails
FOR EACH ROW
BEGIN
DECLARE new_quantity INT;
DECLARE current_inventory INT;
DECLARE min_threshold INT;

-- Get the new quantity from the inserted row
SET new_quantity = NEW.quantityOrdered;

-- Get the current inventory for the product
SELECT quantityInStock, restockThreshold INTO current_inventory, min_threshold
FROM Inventory
WHERE productCode = NEW.productCode;

-- Decrease the inventory
UPDATE Inventory
SET quantityInStock = quantityInStock - new_quantity
WHERE productCode = NEW.productCode;

-- Check if inventory falls below threshold
IF (current_inventory - new_quantity) < min_threshold THEN
    UPDATE Inventory
    SET restockNeeded = 1
    WHERE productCode = NEW.productCode;
END IF;

END;
//
DELIMITER ;