Stored Procedures & Triggers in SQLite with Python – Fun Beginner Guide

Stored Procedures and Triggers with SQL: A Simple and Fun Tutorial for Everyone

Welcome back to our exciting SQL adventure! In our previous tutorials, we learned how to manage data with commands like SELECT, JOIN, and transactions in our toy store database. Now, let’s dive into something super cool: stored procedures and triggers. These are like magic spells that make your database smarter by automating tasks and responding to changes. This tutorial is designed to be easy to understand, useful for beginners and experienced users, and covers key aspects of stored procedures and triggers. We’ll use SQLite and Python with our toystore.db database, keeping it fun and simple like casting spells in a magical toy shop!

---

What are Stored Procedures and Triggers?

Imagine you run a toy store and have a favorite recipe for organizing toys—like always checking stock and updating prices in one go. Instead of writing the same instructions every time, you could save that recipe as a magic spell to use whenever you want. That’s what a stored procedure does—it’s a saved set of SQL commands you can run with one call.

Now, imagine a magic alarm that automatically updates your inventory list whenever a toy is sold. That’s a trigger—it automatically runs specific commands when something happens in your database, like adding or deleting data.

In our toy store, we’ll:

• Create a stored procedure to process a toy sale.
• Create a trigger to automatically log sales in a history table.
• Use SQLite and Python to make it all happen.

---

Why Learn Stored Procedures and Triggers?

Stored procedures and triggers are awesome because:

• They’re Time-Savers: They let you reuse and automate tasks, like magic shortcuts.
• They’re Simple: Once you set them up, they’re easy to use.
• They’re Powerful: They make your database smarter and more organized.
• They’re Useful: They’re used in apps, websites, and games to handle repetitive tasks.
• They’re Fun: It’s like programming your database to do tricks for you!

Let’s explore these magical tools in our toy store!

---

Getting Started

We’ll use Python with SQLite to run our SQL commands, just like in our previous tutorials. Make sure you have Python installed (download it from python.org if needed). SQLite comes with Python, so no extra setup is required. You can also use DB Browser for SQLite to see your data visually, but we’ll focus on Python code for clarity.

Important Note: SQLite has limited support for stored procedures compared to databases like MySQL or PostgreSQL. It doesn’t natively support stored procedures, but we can simulate them using Python functions that run SQL commands. Triggers, however, are fully supported in SQLite. We’ll show both concepts clearly, using SQLite for triggers and Python to mimic stored procedures.

We’ll work with our toystore.db database, using the Toys table, a Sales table, and a new SaleHistory table to log sales automatically. Here’s the setup code:

import sqlite3

# Connect to the database
conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Create Toys table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Toys (
        ToyID INTEGER PRIMARY KEY,
        Name TEXT,
        Type TEXT,
        Price REAL,
        Stock INTEGER
    )
''')

# Create Sales table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Sales (
        SaleID INTEGER PRIMARY KEY,
        ToyID INTEGER,
        Quantity INTEGER,
        TotalPrice REAL,
        FOREIGN KEY (ToyID) REFERENCES Toys(ToyID)
    )
''')

# Create SaleHistory table for triggers
cursor.execute('''
    CREATE TABLE IF NOT EXISTS SaleHistory (
        HistoryID INTEGER PRIMARY KEY,
        SaleID INTEGER,
        ToyName TEXT,
        SaleTime TEXT
    )
''')

# Clear existing data to avoid duplicates
cursor.execute("DELETE FROM Toys")
cursor.execute("DELETE FROM Sales")
cursor.execute("DELETE FROM SaleHistory")

# Add toys
cursor.execute("INSERT INTO Toys (Name, Type, Price, Stock) VALUES ('Robot', 'Action Figure', 30.00, 10)")
cursor.execute("INSERT INTO Toys (Name, Type, Price, Stock) VALUES ('Jigsaw', 'Puzzle', 10.00, 15)")
cursor.execute("INSERT INTO Toys (Name, Type, Price, Stock) VALUES ('Teddy', 'Stuffed Animal', 15.00, 8)")

conn.commit()
conn.close()
print("Toy store database ready for stored procedures and triggers!")

What’s Happening?

• Toys table: Stores toy details with Stock to track inventory.
• Sales table: Records sales with ToyID, Quantity, and TotalPrice.
• SaleHistory table: Logs sales with a timestamp for tracking (used by triggers).
• We added three toys to start.

Our Toys table looks like this:

ToyID	Name	Type	Price	Stock
1	Robot	Action Figure	30.00	10
2	Jigsaw	Puzzle	10.00	15
3	Teddy	Stuffed Animal	15.00	8

---

What is a Stored Procedure?

A stored procedure is a saved set of SQL commands you can run with one call, like a recipe you store for later. In SQLite, we can’t create stored procedures directly, but we can mimic them with Python functions that run SQL commands.

For example, let’s create a “stored procedure” (Python function) to process a toy sale, which:

1. Checks if there’s enough stock.
2. Updates the stock in the Toys table.
3. Adds a sale record to the Sales table.

Example: Simulating a Stored Procedure

import sqlite3

def process_sale(toy_id, quantity):
    conn = sqlite3.connect('toystore.db')
    cursor = conn.cursor()
    
    try:
        # Start transaction
        cursor.execute("SELECT Stock, Price, Name FROM Toys WHERE ToyID = ?", (toy_id,))
        result = cursor.fetchone()
        if not result:
            raise Exception("Toy not found!")
        stock, price, toy_name = result
        
        if stock < quantity:
            raise Exception(f"Not enough {toy_name} in stock!")
        
        # Update stock
        cursor.execute("UPDATE Toys SET Stock = Stock - ? WHERE ToyID = ?", (quantity, toy_id))
        
        # Add sale
        total_price = quantity * price
        cursor.execute("INSERT INTO Sales (ToyID, Quantity, TotalPrice) VALUES (?, ?, ?)",
                      (toy_id, quantity, total_price))
        
        conn.commit()
        print(f"Sale of {quantity} {toy_name}(s) completed!")
        
        # Show results
        cursor.execute("SELECT * FROM Toys WHERE ToyID = ?", (toy_id,))
        print("Updated Toy:", cursor.fetchone())
        cursor.execute("SELECT * FROM Sales WHERE ToyID = ?", (toy_id,))
        print("Sales:", cursor.fetchall())
    
    except Exception as e:
        conn.rollback()
        print(f"Error: {e}. Sale cancelled!")
    
    conn.close()

# Test the procedure
process_sale(1, 2)  # Sell 2 Robots

Output:

Sale of 2 Robot(s) completed!
Updated Toy: (1, 'Robot', 'Action Figure', 30.0, 8)
Sales: [(1, 1, 2, 60.0)]

---

What is a Trigger?

A trigger is an automatic action that runs when something happens in a table, like adding, updating, or deleting data. For example, we can create a trigger to log every sale in the SaleHistory table with a timestamp.

Example: Creating a Trigger

import sqlite3
from datetime import datetime

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Create trigger
cursor.execute('''
    CREATE TRIGGER IF NOT EXISTS log_sale
    AFTER INSERT ON Sales
    FOR EACH ROW
    BEGIN
        INSERT INTO SaleHistory (SaleID, ToyName, SaleTime)
        SELECT NEW.SaleID, Toys.Name, DATETIME('now')
        FROM Toys 
        WHERE Toys.ToyID = NEW.ToyID;
    END;
''')

conn.commit()

# Test the trigger by adding a sale
try:
    cursor.execute("UPDATE Toys SET Stock = Stock - 3 WHERE ToyID = 3")
    cursor.execute("INSERT INTO Sales (ToyID, Quantity, TotalPrice) VALUES (3, 3, 45.00)")
    conn.commit()
    print("Sale of 3 Teddies added!")
    
    # Check SaleHistory
    cursor.execute("SELECT * FROM SaleHistory")
    print("Sale History:", cursor.fetchall())

except:
    conn.rollback()
    print("Sale failed!")

conn.close()

Output (timestamp will vary):

Sale of 3 Teddies added!
Sale History: [(1, 3, 'Teddy', '2025-09-03 00:15:23')]

---

Combining Stored Procedures and Triggers

process_sale(2, 2)  # Sell 2 Jigsaws

# Check SaleHistory
conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM SaleHistory")
print("Sale History:", cursor.fetchall())
conn.close()

Output:

Sale of 2 Jigsaw(s) completed!
Updated Toy: (2, 'Jigsaw', 'Puzzle', 10.0, 13)
Sales: [(2, 2, 2, 20.0)]
Sale History: [(1, 3, 'Teddy', '2025-09-03 00:15:23'), (2, 2, 'Jigsaw', '2025-09-03 00:15:25')]

---

Tips for Success

1. Start Simple: Try one trigger or procedure at a time.
2. Test Triggers: Insert data to see if your trigger works.
3. Use Transactions: Combine with transactions for safety.
4. Check SQLite Limits: SQLite doesn’t support stored procedures natively, so use Python functions.
5. Practice: Try triggers for logging updates or procedures for complex tasks like discounts.

---

Recap: Why Learn Stored Procedures and Triggers?

These tools are like magic for your database:

• They Save Time: Reuse code and automate tasks.
• They’re Safe: Triggers ensure actions happen consistently.
• They’re Useful: Used in apps, stores, and games.
• They’re Fun: It’s like programming your database to be smart!

---

Common Questions

1. Does SQLite support stored procedures?

Not natively, but Python functions can mimic them.

2. Do triggers work in other databases?

Yes, MySQL, PostgreSQL, and others support triggers and stored procedures with similar syntax.

3. Can triggers cause errors?

Yes, if not written carefully, so test them with small data first.

4. Can I have multiple triggers?

Yes, but each must have a unique name.

---

🧩 Challenge for Readers

Ready to test your SQL wizardry? Try this challenge:

🎯 Challenge:
Create your own trigger that updates a LowStockAlerts table whenever a toy's stock drops below 5.

Steps:

1. Create a new table called LowStockAlerts with columns for ToyID, Name, and AlertTime.
2. Write a trigger that runs AFTER an update to the Toys table’s Stock column.
3. If Stock < 5, insert a new row into LowStockAlerts with the toy name and the current time.

💡 Bonus: Combine this with your existing Python “stored procedure” so stock updates from sales automatically trigger the alert!

---

Wrapping Up

Stored procedures and triggers are like magic spells that make your database smarter and safer. In this tutorial, we used a Python function to simulate a stored procedure for toy sales and created a trigger to log sales automatically in our toystore.db database. Whether you’re a 6th grader or a pro coder, these tools are fun and powerful for automating database tasks.

Try creating your own database for a game or library and experiment with triggers or procedures. Use DB Browser for SQLite to see your data or keep coding in Python. With stored procedures and triggers, you’re now a database wizard, ready to automate your data like a superhero!

Happy SQL adventures, and keep casting those database spells!

---

🚀 What’s Next?

Loved this tutorial? Want to keep leveling up your SQL and Python skills?

• 🔄 Try building a small inventory or game database from scratch using what you’ve learned.
• 💬 Share your version of the “Low Stock Alert” challenge in the comments below!
• 📧 Subscribe or follow for more fun, beginner-friendly tutorials using real code and creative examples.
• 🧙‍♂️ Keep casting those SQL spells and automating your data like a true database wizard!

← Previous Next →

Master SQL Joins & Table Relationships with Fun Python & SQLite Examples

Joining Tables and Relationships with SQL: A Simple and Fun Tutorial for Everyone

Learn how to join tables and model relationships in Python using SQLite. This beginner-friendly tutorial shows how to connect data from multiple tables in a toy store database.

Welcome back to our exciting SQL adventure! In our last tutorial, Filtering, Sorting, and Aggregating Data, we learned how to pick out specific data, arrange it neatly, and summarize it using SQL commands in our toy store database. Now, let’s explore something even cooler: joining tables and understanding relationships in a database. This is like connecting different toy boxes to tell a bigger story, such as linking toys to their owners. This tutorial covers key aspects of joining tables and relationships. We’ll continue using SQLite and Python with our toystore.db database, keeping it fun and simple like organizing a treasure hunt!

---

What are Joining Tables and Relationships?

Imagine your toy store has two notebooks: one lists all your toys, and another lists customers who buy them. Sometimes, you want to combine these notebooks to answer questions like, “Which customer bought which toy?” This is where joining tables comes in—it lets you connect information from different tables in a database.

A relationship is how the tables are connected. For example, a customer might be linked to a toy they bought through a special number (like a toy’s ID). SQL helps you join tables to see this combined information, making your data more powerful and fun to explore!

In our toy store, we’ll:

• Create two tables: Toys and Customers.
• Link them with a third table, Purchases, to show which customer bought which toy.
• Use SQL JOIN commands to combine the data.

---

Why Learn Joining Tables and Relationships?

Joining tables and understanding relationships are awesome because:

• They’re Simple: The SQL commands are like connecting puzzle pieces.
• They’re Powerful: You can answer complex questions by combining data from multiple tables.
• They’re Useful: Joins are used in apps, websites, games, and school projects to connect information.
• They’re Fun: It’s like being a detective, linking clues to solve a mystery!
• They Build on SQL: If you know SELECT and WHERE from our last tutorials, you’re ready for joins.

• Powerful for Apps & Reporting: Joins let you create customer dashboards or sales reports.
• Data Analysis Essential: Anywhere you need to correlate data—like users and their actions—you’ll use joins.
• Career Skill: Understanding joins is foundational for data analysts, backend developers, and QA engineers.

Let’s dive into our toy store and learn how to join tables!

---

Getting Started

We’ll use Python with SQLite to run our SQL commands, just like before. Make sure you have Python installed (download it from python.org if needed). SQLite is built into Python, so no extra setup is required. You can also use DB Browser for SQLite to see your tables visually, but we’ll focus on Python code for clarity.

We’ll work with our toystore.db database and create three tables:

• Toys: Stores toy details (from our last tutorial).
• Customers: Stores customer names and contact info.
• Purchases: Links customers to the toys they bought, showing relationships.

Here’s the code to set up the database and tables:

import sqlite3

# Connect to the database
conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Create Toys table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Toys (
        ToyID INTEGER PRIMARY KEY,
        Name TEXT,
        Type TEXT,
        Price REAL
    )
''')

# Create Customers table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Customers (
        CustomerID INTEGER PRIMARY KEY,
        Name TEXT,
        Email TEXT
    )
''')

# Create Purchases table to link Toys and Customers
cursor.execute('''
    CREATE TABLE IF NOT EXISTS Purchases (
        PurchaseID INTEGER PRIMARY KEY,
        CustomerID INTEGER,
        ToyID INTEGER,
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
        FOREIGN KEY (ToyID) REFERENCES Toys(ToyID)
    )
''')

# Clear existing data to avoid duplicates
cursor.execute("DELETE FROM Toys")
cursor.execute("DELETE FROM Customers")
cursor.execute("DELETE FROM Purchases")

# Add toys
cursor.execute("INSERT INTO Toys (Name, Type, Price) VALUES ('Robot', 'Action Figure', 30.00)")
cursor.execute("INSERT INTO Toys (Name, Type, Price) VALUES ('Jigsaw', 'Puzzle', 10.00)")
cursor.execute("INSERT INTO Toys (Name, Type, Price) VALUES ('Teddy', 'Stuffed Animal', 15.00)")

# Add customers
cursor.execute("INSERT INTO Customers (Name, Email) VALUES ('Alice', 'alice@email.com')")
cursor.execute("INSERT INTO Customers (Name, Email) VALUES ('Bob', 'bob@email.com')")

# Add purchases (Alice bought Robot and Jigsaw, Bob bought Teddy)
cursor.execute("INSERT INTO Purchases (CustomerID, ToyID) VALUES (1, 1)")
cursor.execute("INSERT INTO Purchases (CustomerID, ToyID) VALUES (1, 2)")
cursor.execute("INSERT INTO Purchases (CustomerID, ToyID) VALUES (2, 3)")

conn.commit()
conn.close()
print("Toy store database with relationships ready!")

What’s Happening?

• Toys table: Stores toys with ToyID, Name, Type, and Price.
• Customers table: Stores customers with CustomerID, Name, and Email.
• Purchases table: Links customers to toys using CustomerID and ToyID. The FOREIGN KEY ensures CustomerID matches a CustomerID in the Customers table, and ToyID matches a ToyID in the Toys table.

This setup creates a relationship between tables: the Purchases table connects Customers and Toys like a bridge.

---

Understanding Relationships

A relationship shows how tables are connected. In our toy store:

• The Purchases table links Customers to Toys using CustomerID and ToyID.
• This is called a many-to-many relationship because one customer can buy many toys, and one toy can be bought by many customers.
• The FOREIGN KEY ensures the IDs match, keeping the data organized and valid.
• Foreign keys enforce referential integrity—ensuring the CustomerID in Purchases actually refers to a valid customer. Trying to insert a purchase with a non-existing CustomerID would result in an error.

Other types of relationships include:

• One-to-many: One customer can buy many toys, but each toy is linked to one purchase.
• One-to-one: One customer can have one favorite toy (rarely used).

Joins let us combine these tables to see the full picture!

---

Joining Tables with SQL

SQL JOIN commands combine data from multiple tables. There are several types of joins, but we’ll focus on the most common ones:

• INNER JOIN: Shows only rows where there’s a match in both tables.
• LEFT JOIN: Shows all rows from the first table, even if there’s no match in the second.
• RIGHT JOIN: Shows all rows from the second table (less common in SQLite).
• FULL JOIN: Shows all rows from both tables (not supported in SQLite).

1. INNER JOIN: Finding Matches

Show each purchase with the customer’s name and the toy’s name.

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

print("Purchases with Customer and Toy Names:")
cursor.execute('''
    SELECT Customers.Name, Toys.Name, Toys.Price
    FROM Purchases
    INNER JOIN Customers ON Purchases.CustomerID = Customers.CustomerID
    INNER JOIN Toys ON Purchases.ToyID = Toys.ToyID
''')
for purchase in cursor.fetchall():
    print(purchase)

conn.close()

Output:

('Alice', 'Robot', 30.0)
('Alice', 'Jigsaw', 10.0)
('Bob', 'Teddy', 15.0)

2. LEFT JOIN: Including All from One Table

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

# Add Charlie (no purchases)
cursor.execute("INSERT INTO Customers (Name, Email) VALUES ('Charlie', 'charlie@email.com')")
conn.commit()

# LEFT JOIN to show all customers
print("All Customers and Their Purchases (if any):")
cursor.execute('''
    SELECT Customers.Name, Toys.Name
    FROM Customers
    LEFT JOIN Purchases ON Customers.CustomerID = Purchases.CustomerID
    LEFT JOIN Toys ON Purchases.ToyID = Toys.ToyID
''')
for result in cursor.fetchall():
    print(result)

conn.close()

Output:

('Alice', 'Robot')
('Alice', 'Jigsaw')
('Bob', 'Teddy')
('Charlie', None)

Join Type	Includes Rows	Example Use
INNER JOIN	Only matching rows	Who bought a toy?
LEFT JOIN	All from left + matches	Which customers have no purchases?

3. Combining Joins with Filtering and Sorting

import sqlite3

conn = sqlite3.connect('toystore.db')
cursor = conn.cursor()

print("Purchases of Toys Over $15, Sorted by Price:")
cursor.execute('''
    SELECT Customers.Name, Toys.Name, Toys.Price
    FROM Purchases
    INNER JOIN Customers ON Purchases.CustomerID = Customers.CustomerID
    INNER JOIN Toys ON Purchases.ToyID = Toys.ToyID
    WHERE Toys.Price > 15.00
    ORDER BY Toys.Price ASC
''')
for purchase in cursor.fetchall():
    print(purchase)

conn.close()

Output:

('Alice', 'Robot', 30.0)

---

Best Practices for Writing SQL JOINs

• Use explicit JOIN ... ON syntax instead of comma-based joins for clarity. :contentReference[oaicite:0]{index=0}
• Index foreign key columns like CustomerID and ToyID to speed up joins. :contentReference[oaicite:1]{index=1}
• Use table aliases (e.g., c for Customers, t for Toys) for cleaner queries. :contentReference[oaicite:2]{index=2}
• Select only needed columns—avoid using * to improve performance. :contentReference[oaicite:3]{index=3}

---

Tips for Success

• Start Simple: Try one join at a time.
• Check Relationships: Make sure your tables are linked with keys.
• Test with SELECT: Use SELECT to check your join results.
• Use Clear Names: Write Customers.Name instead of just Name.
• Practice: Create your own datasets and practice joining!

---

Common Questions

1. Are joins hard to learn?

No! They’re like matching pieces in a puzzle.

2. Do joins work with other databases?

Yes, the syntax is very similar across platforms.

3. What if I join the wrong tables?

You might get incorrect results or no data, so double-check your ON clauses.

4. Can I join more than two tables?

Absolutely! Like we did with three tables.

---

Wrapping Up

Joining tables and understanding relationships in SQL is like linking toy boxes to tell a bigger story. In this tutorial, we used INNER JOIN and LEFT JOIN to connect our Toys, Customers, and Purchases tables in toystore.db, answering questions like “Who bought what?” We also combined joins with filtering and sorting for extra power.

joins are a fun and essential skill for managing data.

Try creating your own database for something you love, like movies and actors, and practice joining tables. Use DB Browser for SQLite to see your data visually or keep experimenting with Python. With SQL joins, you’re now a data detective, ready to connect and explore information like a superhero!

Happy SQL adventures, and keep connecting those tables!

← Previous Next →