DEV Community

Cover image for Stored Procedures, Functions, and Triggers
arjun
arjun

Posted on

1 1 1 1

Stored Procedures, Functions, and Triggers

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Usage:

CALL GetUserOrderHistory(1);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Test the Trigger:

Insert a new order:

INSERT INTO orders (order_id, user_id, total, product_id)
VALUES (104, 3, 150, 1);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Real-World Example

Scenario: Automate Common Tasks

  1. Fetch User Order History with a Stored Procedure:

    The GetUserOrderHistory procedure simplifies retrieving order data. This can be integrated into the backend for user account dashboards.

  2. Update Inventory with a Trigger:

    Automatically reducing inventory stock ensures data consistency and eliminates manual updates.

  3. 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

  1. What is the difference between stored procedures and functions?
  2. How do AFTER and BEFORE triggers differ?
  3. Write a stored procedure to calculate the total sales for a given date range.
  4. 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!

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay