Day 6: Stored Procedures, Functions, and Triggers
Date: January 24, 2025
Today, we delve into Stored Procedures, Functions, and Triggers—powerful tools in SQL that enhance automation, modularity, and efficiency in database management. By leveraging these features, we can simplify complex operations, reuse code, and automate repetitive tasks, all while improving database performance and maintainability.
Concepts
1. Stored Procedures
A stored procedure is a precompiled set of SQL statements stored in the database. It can be executed as needed, making it a great tool for repetitive or complex operations.
Key Features:
- Reduces repetitive code.
- Improves performance by precompiling queries.
- Can accept input parameters and return results.
Syntax:
CREATE PROCEDURE procedure_name(parameters)
BEGIN
SQL statements;
END;
2. Functions
A function is similar to a stored procedure but is designed to return a single value. Functions are commonly used for calculations, transformations, or validations.
Key Features:
- Returns a value.
- Can be used in SQL queries like SELECT statements.
- Typically used for modular and reusable logic.
Syntax:
CREATE FUNCTION function_name(parameters)
RETURNS return_datatype
BEGIN
SQL statements;
RETURN value;
END;
3. Triggers
A trigger is a special type of stored procedure that automatically executes in response to specific events on a table (e.g., INSERT, UPDATE, DELETE).
Key Features:
- Automates tasks such as logging or data validation.
- Executes before or after an event.
Syntax:
CREATE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
SQL statements;
END;
Practice
Scenario: Users and Orders Tables
users table:
| id | name | email |
|----|-------|--------------------|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
| 3 | Carol | carol@example.com |orders table:
| order_id | user_id | total | product_id |
|----------|---------|-------|------------|
| 101 | 1 | 250 | 1 |
| 102 | 2 | 400 | 2 |
| 103 | 1 | 300 | 3 |inventory table:
| product_id | stock |
|------------|-------|
| 1 | 50 |
| 2 | 30 |
| 3 | 40 |
Practice Examples
1. Write a Stored Procedure to Fetch User Order History
This procedure accepts a user_id
as input and returns all orders placed by the user.
CREATE PROCEDURE GetUserOrderHistory(IN input_user_id INT)
BEGIN
SELECT orders.order_id, orders.total, users.name
FROM orders
INNER JOIN users ON orders.user_id = users.id
WHERE orders.user_id = input_user_id;
END;
Usage:
CALL GetUserOrderHistory(1);
Result:
| order_id | total | name |
|----------|-------|-------|
| 101 | 250 | Alice |
| 103 | 300 | Alice |
2. Create a Trigger to Update Inventory After an Order
This trigger decreases the stock of a product in the inventory
table after a new order is placed in the orders
table.
CREATE TRIGGER UpdateInventoryAfterOrder
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE inventory
SET stock = stock - 1
WHERE product_id = NEW.product_id;
END;
Test the Trigger:
Insert a new order:
INSERT INTO orders (order_id, user_id, total, product_id)
VALUES (104, 3, 150, 1);
Updated Inventory:
| product_id | stock |
|------------|-------|
| 1 | 49 |
3. Automate Email Notifications for New Orders (Concept-Level)
While SQL alone cannot send emails, the concept involves creating a trigger that logs the email details into a separate table. The backend application can then process the email queue and send notifications.
CREATE TRIGGER LogEmailNotification
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO email_notifications (user_id, email_body)
VALUES (NEW.user_id, CONCAT('Thank you for your order with ID ', NEW.order_id));
END;
Real-World Example
Scenario: Automate Common Tasks
Fetch User Order History with a Stored Procedure:
TheGetUserOrderHistory
procedure simplifies retrieving order data. This can be integrated into the backend for user account dashboards.Update Inventory with a Trigger:
Automatically reducing inventory stock ensures data consistency and eliminates manual updates.Email Notifications with a Trigger:
Logging email notifications in a queue streamlines communication with users. This can be paired with an email service like SendGrid or SMTP for delivery.
Interview Preparation
- What is the difference between stored procedures and functions?
- How do AFTER and BEFORE triggers differ?
- Write a stored procedure to calculate the total sales for a given date range.
- How can triggers be used to maintain data integrity?
Outcome for the Day
By the end of Day 6, you should:
- Understand the differences between stored procedures, functions, and triggers.
- Write reusable SQL code with stored procedures and functions.
- Automate database tasks with triggers.
In Day 7, we’ll explore Database Relationships and Constraints, focusing on how to build robust, reliable database designs. Let me know if you're ready to dive deeper!
Top comments (0)