SQL Views, Stored Procedures & Triggers: Complete Guide

Prerequisite: This chapter builds on the SQL Beginner Course. You should be comfortable with SELECT, INSERT, UPDATE, DELETE and JOINs before proceeding.

In This Chapter

Understanding and Creating Views
Creating Stored Procedures
Understanding and Creating Triggers

1 Understanding and Creating Views

Top

A view is a saved query that acts like a virtual table. It doesn't store data itself — it runs the underlying query every time you access it. Think of it as a bookmark for a complex query that you can reuse by name.

Why Use Views?

Simplify complex queries — write a 20-line JOIN once, then SELECT * FROM my_view
Security — expose only certain columns to certain users (hide salary, SSN, etc.)
Consistency — everyone in the team uses the same query logic
Abstraction — change the underlying tables without breaking application code

Creating a View

SQL

-- A view that shows order details with customer names
CREATE VIEW order_details AS
SELECT
    c.name       AS customer,
    c.city,
    o.product,
    o.amount,
    o.order_date,
    o.amount * o.quantity AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Now use it exactly like a table:

SQL

-- Simple and clean — no need to remember the JOIN
SELECT customer, product, total
FROM order_details
WHERE total > 100
ORDER BY total DESC;

Modifying and Dropping Views

SQL

-- Replace with an updated definition
CREATE OR REPLACE VIEW order_details AS
SELECT c.name AS customer, o.product, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- Delete a view
DROP VIEW IF EXISTS order_details;

View Limitations

Can Do	Can't Do (Usually)
SELECT, WHERE, ORDER BY, GROUP BY	INSERT/UPDATE/DELETE on views with JOINs
Join multiple tables	Use variables or parameters (use procedures instead)
Nest views (view of a view)	Index a view directly (MySQL)

Performance note: Views don't cache results — the underlying query runs every time. A view on a complex 5-table JOIN will be just as slow as running that JOIN directly. For performance-critical queries, consider materialized views (PostgreSQL) or creating summary tables.

Key Takeaways

A view is a saved SELECT query that behaves like a virtual table
CREATE VIEW name AS SELECT ... — then SELECT * FROM name
Use views to simplify complex queries, enforce security, and ensure consistency
Views don't store data or improve performance — the query runs each time

2 Creating Stored Procedures

Top

A stored procedure is a reusable block of SQL code stored in the database. Unlike views, procedures can accept parameters, use variables, contain control flow (IF/ELSE, loops), and execute multiple statements. Think of them as functions for your database.

Creating a Simple Procedure

SQL

DELIMITER //

CREATE PROCEDURE get_expensive_orders(
    IN min_amount DECIMAL(10,2)
)
BEGIN
    SELECT c.name, o.product, o.amount
    FROM customers c
    JOIN orders o ON c.id = o.customer_id
    WHERE o.amount >= min_amount
    ORDER BY o.amount DESC;
END //

DELIMITER ;

Call it with different values:

SQL

CALL get_expensive_orders(50);   -- orders >= $50
CALL get_expensive_orders(100);  -- orders >= $100

Why DELIMITER //? MySQL uses ; to end statements, but the procedure body contains semicolons too. DELIMITER // temporarily changes the statement terminator so MySQL knows the entire CREATE PROCEDURE ... END is one statement. You switch back with DELIMITER ; at the end.

Parameter Types: IN, OUT, INOUT

SQL

DELIMITER //

CREATE PROCEDURE get_customer_stats(
    IN  customer_name  VARCHAR(100),   -- input
    OUT total_orders   INT,            -- output
    OUT total_spent    DECIMAL(10,2)  -- output
)
BEGIN
    SELECT COUNT(*), SUM(amount)
    INTO total_orders, total_spent
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE c.name = customer_name;
END //

DELIMITER ;

-- Call and read the output
CALL get_customer_stats('Sara Ahmed', @orders, @spent);
SELECT @orders AS orders, @spent AS spent;

Result

orders | spent
-------|------
2      | 69.98

Type	Direction	Use Case
`IN`	Caller → Procedure	Pass a customer name, date range, or ID
`OUT`	Procedure → Caller	Return a calculated total, count, or status
`INOUT`	Both directions	Pass a value, modify it, return the modified version

Procedures with Control Flow

SQL

DELIMITER //

CREATE PROCEDURE classify_customer(
    IN  cust_name VARCHAR(100),
    OUT tier      VARCHAR(20)
)
BEGIN
    DECLARE total DECIMAL(10,2);

    SELECT COALESCE(SUM(amount), 0) INTO total
    FROM orders o JOIN customers c ON c.id = o.customer_id
    WHERE c.name = cust_name;

    IF total >= 200 THEN
        SET tier = 'Gold';
    ELSEIF total >= 50 THEN
        SET tier = 'Silver';
    ELSE
        SET tier = 'Bronze';
    END IF;
END //

DELIMITER ;

Key Takeaways

Stored procedures are reusable SQL programs stored in the database
They accept parameters (IN/OUT/INOUT) and can contain logic (IF/ELSE, loops)
Call them with CALL procedure_name(args)
Use DELIMITER // in MySQL to avoid semicolon conflicts
Great for encapsulating business logic, data validation, and complex operations

3 Understanding and Creating Triggers

Top

A trigger is a stored procedure that runs automatically when a specific event happens on a table — an INSERT, UPDATE, or DELETE. You don't call triggers manually; they fire on their own. They're used for auditing, validation, and maintaining derived data.

Trigger Timing and Events

Timing	Event	Use Case
`BEFORE INSERT`	Before a new row is added	Validate data, set default values
`AFTER INSERT`	After a new row is added	Log the insertion, update summary tables
`BEFORE UPDATE`	Before a row is changed	Validate new values, preserve old values
`AFTER UPDATE`	After a row is changed	Audit trail, sync related tables
`BEFORE DELETE`	Before a row is removed	Prevent deletion, archive data
`AFTER DELETE`	After a row is removed	Log the deletion, update counts

Creating an Audit Trigger

SQL

-- Create an audit log table
CREATE TABLE order_audit (
    audit_id    INT PRIMARY KEY AUTO_INCREMENT,
    order_id    INT,
    action      VARCHAR(10),
    old_amount  DECIMAL(8,2),
    new_amount  DECIMAL(8,2),
    changed_at  DATETIME DEFAULT NOW()
);

DELIMITER //

-- Log every price change
CREATE TRIGGER trg_order_price_change
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.amount != NEW.amount THEN
        INSERT INTO order_audit (order_id, action, old_amount, new_amount)
        VALUES (NEW.id, 'UPDATE', OLD.amount, NEW.amount);
    END IF;
END //

DELIMITER ;

Now when an order price changes, the audit happens automatically:

SQL

UPDATE orders SET amount = 34.99 WHERE id = 101;

-- Check the audit log
SELECT * FROM order_audit;

Result

audit_id | order_id | action | old_amount | new_amount | changed_at
---------|----------|--------|------------|------------|-------------------
1        | 101      | UPDATE | 29.99      | 34.99      | 2025-04-04 14:30:00

NEW and OLD References

Reference	Available In	What It Holds
`NEW.column`	INSERT, UPDATE triggers	The value being written (new data)
`OLD.column`	UPDATE, DELETE triggers	The value before the change (old data)

BEFORE Trigger: Auto-Set Values

SQL

DELIMITER //

-- Automatically uppercase product names before insert
CREATE TRIGGER trg_normalize_product
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.product = UPPER(NEW.product);
END //

DELIMITER ;

Use triggers sparingly. They run invisibly, making debugging harder. A developer looking at an UPDATE orders statement won't know that a trigger is also writing to an audit table unless they check. Document your triggers, and prefer application-level logic for complex business rules. See Advanced Triggers for performance considerations.

Managing Triggers

SQL

-- List all triggers
SHOW TRIGGERS;

-- Delete a trigger
DROP TRIGGER IF EXISTS trg_order_price_change;

Key Takeaways

Triggers fire automatically on INSERT, UPDATE, or DELETE events
BEFORE triggers can modify data before it's written; AFTER triggers react after
Use NEW.col for incoming values and OLD.col for previous values
Great for auditing and auto-setting values; use sparingly for complex logic
Always document triggers — they're invisible to developers reading the main query

What's next? Now that you can create views, procedures, and triggers, the next chapter covers Database Normalization — the rules for designing efficient, redundancy-free database schemas (1NF through BCNF).

All Chapters in SQL Intermediate Course

Continue Learning

Back to CourseSQL Intermediate Course Next Chapter Database Normalization

Views, Stored Procedures & Triggers

In This Chapter

1 Understanding and Creating Views

Why Use Views?

Creating a View

Modifying and Dropping Views

View Limitations

Key Takeaways

2 Creating Stored Procedures

Creating a Simple Procedure

Parameter Types: IN, OUT, INOUT

Procedures with Control Flow

Key Takeaways

3 Understanding and Creating Triggers

Trigger Timing and Events

Creating an Audit Trigger

NEW and OLD References

BEFORE Trigger: Auto-Set Values

Managing Triggers

Key Takeaways

All Chapters in SQL Intermediate Course

Continue Learning

Courses

Services

Support

Connect