Mastering MySQL Stored Routines: Procedures, Triggers, and Functions in Action

[akash-coded]

Exercise Title:

"Mastering MySQL Stored Routines: Procedures, Triggers, and Functions in Action"

Introduction:

This advanced exercise is designed for experienced SQL users to deepen their understanding and skills in creating and using MySQL stored routines, including stored procedures, triggers, and functions. You'll explore different parameter types in stored procedures, various triggers, and the strategic applications of stored functions. The scenarios will be based on the classicmodels database, providing a realistic context for applying these concepts.

Part 1: Stored Procedures

Scenario: Order Management System Enhancement

Enhance the order management system by creating stored procedures for common operations.

Task 1.1: Creating a New Order

• Objective: Develop a stored procedure to create a new order, including order details.
• Procedure Requirements:
  • Parameters: Customer ID, Order Date, Required Date, Status, and an array of product-line items (Product ID, Quantity, Price).
  • The procedure should insert a new order and then insert multiple order details from the product-line items array.
  • Use transaction control to ensure data integrity.
• SQL Constructs: CREATE PROCEDURE, BEGIN ... END, DECLARE, transaction control (START TRANSACTION, COMMIT, ROLLBACK)

Task 1.2: Updating Order Status

• Objective: Implement a stored procedure to update the status of an existing order.
• Procedure Requirements:
  • Parameters: Order ID, New Status.
  • The procedure should validate the existence of the order before updating.
• SQL Constructs: CREATE PROCEDURE, IF ... THEN, UPDATE

Part 2: Triggers

Scenario: Automated Audit Trail

Create triggers to automatically record changes to critical tables for audit purposes.

Task 2.1: Trigger on Order Updates

• Objective: Develop an AFTER UPDATE trigger on the orders table to record changes in order status.
• Trigger Requirements:
  • Capture the Order ID, old status, new status, and the timestamp of the change.
  • Insert these details into an order_audit table.
• SQL Constructs: CREATE TRIGGER, AFTER UPDATE, NEW, OLD

Task 2.2: Trigger on New Customers

• Objective: Create a BEFORE INSERT trigger on the customers table to validate new customer data.
• Trigger Requirements:
  • Check if the customer's credit limit is within an acceptable range.
  • If not, issue a signal SQLSTATE to abort the insertion.
• SQL Constructs: CREATE TRIGGER, BEFORE INSERT, SIGNAL SQLSTATE '45000'

Part 3: Stored Functions

Scenario: Sales Reporting Enhancements

Use stored functions to support dynamic sales reporting.

Task 3.1: Calculate Total Order Value

• Objective: Create a stored function to calculate the total value of an order.
• Function Requirements:
  • Parameter: Order ID.
  • The function should sum the line item amounts (quantity * price) for the specified order and return the total.
• SQL Constructs: CREATE FUNCTION, RETURNS, SELECT, SUM()

Task 3.2: Determine Customer Tier

• Objective: Implement a stored function to determine a customer's tier based on their total sales.
• Function Requirements:
  • Parameter: Customer ID.
  • The function should calculate the total sales for the customer and return their tier as 'Bronze', 'Silver', 'Gold', or 'Platinum'.
• SQL Constructs: CREATE FUNCTION, RETURNS, CASE

Part 4: Advanced Stored Routine Applications

Scenario: Data Integrity and Business Logic Enforcement

Leverage stored routines to enforce business rules and maintain data integrity across the database.

Task 4.1: Enforcing Minimum Stock Levels

• Objective: Use a trigger to enforce minimum stock levels for products.
• Trigger Requirements:
  • Create a BEFORE INSERT or BEFORE UPDATE trigger on orderdetails to ensure that the order quantity does not exceed the available stock.
  • If the order quantity exceeds the stock, prevent the transaction and raise an appropriate error.
• SQL Constructs: CREATE TRIGGER, BEFORE INSERT, BEFORE UPDATE, SIGNAL SQLSTATE

Task 4.2: Automated Customer Tier Updates

• Objective: Develop a stored procedure that updates customer tiers based on their total sales at the end of each year.
• Procedure Requirements:
  • No explicit parameters.
  • The procedure should calculate total sales per customer for the year and update the customer tier accordingly.
  • Use a cursor to iterate through customers and conditional logic to determine tiers.
• SQL Constructs: CREATE PROCEDURE, cursors, UPDATE, CASE

Part 5: Combining Stored Routines for Complex Workflows

Scenario: End-to-End Order Processing System

Create an integrated system of stored procedures, triggers, and functions to manage the entire lifecycle of orders, from creation to delivery, including inventory management and customer notifications.

Task 5.1: Automated Order Processing Workflow

• Objective: Develop a stored procedure that orchestrates the creation of an order, updates inventory, and notifies the customer.
• Procedure Requirements:
  • Parameters: Customer ID, array of product-line items (Product ID, Quantity), and Order Date.
  • The procedure should create the order, update inventory quantities, and, if successful, send a confirmation to the customer (simulated as an insert into a notifications table).
  • Use transaction control to ensure that the order creation and inventory update are atomic.
  • Include error handling to manage scenarios where inventory is insufficient.
• SQL Constructs: CREATE PROCEDURE, BEGIN ... END, transaction control, error handling (DECLARE ... HANDLER), CALL

Task 5.2: Inventory Replenishment Alert Trigger

• Objective: Implement a trigger that alerts the procurement team when inventory levels for any product fall below a minimum threshold.
• Trigger Requirements:
  • Create an AFTER UPDATE trigger on the products table that checks the new stock level (quantityInStock) after an update.
  • If the stock level is below the minimum threshold, insert an alert into an inventory_alerts table with the product ID, current stock level, and timestamp.
• SQL Constructs: CREATE TRIGGER, AFTER UPDATE, conditional logic (IF)

Part 6: Reporting and Analysis with Stored Functions

Scenario: Dynamic Business Intelligence Reporting

Enhance the reporting capabilities to provide dynamic, on-demand insights into sales performance, product popularity, and customer satisfaction.

Task 6.1: Monthly Sales Performance Function

• Objective: Create a stored function to calculate total sales for any given month and year across all orders.
• Function Requirements:
  • Parameters: Year and Month.
  • The function should sum all order totals from the orderdetails table that match the given year and month and return the total sales figure.
• SQL Constructs: CREATE FUNCTION, RETURNS, date functions (YEAR(), MONTH()), SUM()

Task 6.2: Product Popularity Score Function

• Objective: Develop a stored function that assigns a popularity score to a product based on the number of times it has been ordered and the average order quantity.
• Function Requirements:
  • Parameter: Product ID.
  • The function should calculate the popularity score based on a formula that considers the total number of orders and the average quantity per order for the given product, and return this score.
• SQL Constructs: CREATE FUNCTION, RETURNS, COUNT(), AVG()

Deliverables:

For each task:

• Provide the SQL code for the stored procedure, trigger, or function.
• Include a detailed explanation of the logic behind each stored routine and how it addresses the scenario.
• Offer insights into the choice of SQL constructs and any challenges encountered during implementation.

Part 7: Error Handling in Stored Procedures

Scenario: Robust Order Processing System

Implement error handling in the order processing system to manage exceptions and ensure the system's reliability and accuracy.

Task 7.1: Error Handling in New Order Creation

• Objective: Enhance the stored procedure for creating a new order with error handling to manage potential issues like invalid customer IDs or product stock shortages.
• Procedure Requirements:
  • Incorporate DECLARE ... HANDLER for specific error conditions, such as invalid data or insertion failures.
  • Use conditional logic to check for stock availability before inserting order details.
  • Implement transaction control with ROLLBACK on errors to maintain data integrity.
• SQL Constructs: CREATE PROCEDURE, DECLARE ... HANDLER, IF ... THEN, ROLLBACK, START TRANSACTION, COMMIT

Task 7.2: Graceful Handling of Update Anomalies

• Objective: Update the stored procedure for order status updates to handle anomalies gracefully, such as attempting to update non-existent orders.
• Procedure Requirements:
  • Use error handling to catch exceptions when the specified order ID does not exist.
  • Provide a meaningful error message or code when such a situation arises.
• SQL Constructs: CREATE PROCEDURE, DECLARE ... HANDLER, SIGNAL SQLSTATE '45000', SET MESSAGE_TEXT = '...'

Part 8: Dynamic SQL in Stored Procedures

Scenario: Flexible Reporting System

Create stored procedures that utilize dynamic SQL to generate flexible reports based on input parameters.

Task 8.1: Dynamic Sales Report Generation

• Objective: Develop a stored procedure that generates sales reports based on dynamic input criteria such as date range, customer, or product line.
• Procedure Requirements:
  • Parameters: Report Type (e.g., 'Date Range', 'Customer', 'Product Line'), Start Date, End Date, Customer ID, Product Line.
  • Use CASE or IF statements to construct different SQL queries based on the report type.
  • Employ PREPARE and EXECUTE statements to run the constructed SQL queries.
• SQL Constructs: CREATE PROCEDURE, dynamic SQL (PREPARE, EXECUTE), CASE

Task 8.2: Customizable Product Inventory Report

• Objective: Create a stored procedure for generating product inventory reports that can be customized to include or exclude back-ordered items.
• Procedure Requirements:
  • Parameter: Include Back-Orders (Boolean).
  • Construct the SQL query dynamically based on whether back-ordered items should be included.
  • Use aggregate functions to calculate total inventory levels.
• SQL Constructs: CREATE PROCEDURE, dynamic SQL (PREPARE, EXECUTE), conditional logic (IF ... THEN)

Part 9: Using Triggers for Real-Time Notifications

Scenario: Instant Alert System for Critical Stock Levels

Implement a trigger-based notification system that alerts when product stock levels fall below a critical threshold.

Task 9.1: Trigger for Low Stock Alerts

• Objective: Create an AFTER UPDATE trigger on the products table to check stock levels after any update operation and generate an alert if the stock is critically low.
• Trigger Requirements:
  • Check the new stock level (NEW.stock) after an update.
  • If the stock falls below a predefined critical threshold, insert a notification record into an alerts table with details of the product and the current stock level.
• SQL Constructs: CREATE TRIGGER, AFTER UPDATE, IF ... THEN, INSERT

Submission Guidelines:

• Organize your solutions clearly, with separate sections for each part and task.
• Use comments in your SQL code to explain complex or crucial parts of the logic.
• Test your stored routines to ensure they work as expected and include any test cases or results that demonstrate their functionality.

This exercise aims to simulate real-world scenarios where stored routines can optimize database operations, enforce business logic, and enhance data integrity. It's an opportunity to showcase your ability to integrate advanced SQL techniques into practical solutions.