SQL Views, Triggers

SQL Views, Triggers & Stored Procedures Tutorial

📖 Introduction: Beyond Basic Queries

So far, you've written queries that run once and return results. But what if you need to:

• Reuse the same complex query every day without rewriting it?
• Automatically log who changed what and when?
• Encapsulate business logic (like tax calculation) inside the database?

That's where Views, Triggers, and Stored Procedures come in. They turn your database from a passive storage box into an active, intelligent system.

🛠️ Setting Up Practice Data

sqlCopy
1CREATE DATABASE company_db;
2USE company_db;
3
4CREATE TABLE employees (
5    emp_id INT PRIMARY KEY AUTO_INCREMENT,
6    emp_name VARCHAR(100) NOT NULL,
7    department VARCHAR(50),
8    salary DECIMAL(10,2),
9    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
10    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
11);
12
13CREATE TABLE audit_log (
14    log_id INT PRIMARY KEY AUTO_INCREMENT,
15    action VARCHAR(20),
16    table_name VARCHAR(50),
17    record_id INT,
18    old_values TEXT,
19    new_values TEXT,
20    changed_by VARCHAR(100) DEFAULT CURRENT_USER(),
21    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
22);
23
24INSERT INTO employees (emp_name, department, salary) VALUES
25('Alice Johnson', 'IT', 90000),
26('Bob Smith', 'HR', 55000),
27('Carol White', 'IT', 80000),
28('David Brown', 'Sales', 75000),
29('Eve Davis', 'HR', 60000);

1️⃣ Views — Saved Queries That Act Like Tables

Purpose: A view is a virtual table based on the result of a SELECT query. It doesn't store data itself — it stores the query definition. Every time you query the view, the underlying query runs fresh.

Benefits:

• Simplify complex queries
• Restrict access to sensitive columns (show only what users need)
• Provide consistent reporting interfaces

Example 1: Create a View

Scenario: HR needs a frequent report of employees earning more than $70,000.

sqlCopy
1CREATE VIEW high_earners AS
2SELECT emp_name, department, salary
3FROM employees
4WHERE salary > 70000;

What happens:

• No data is copied or stored separately
• The query definition is saved as high_earners

Example 2: Query a View

You can SELECT from a view exactly like a table:

sqlCopy
1SELECT * FROM high_earners;

Output:

+---------------+------------+----------+
| emp_name      | department | salary   |
+---------------+------------+----------+
| Alice Johnson | IT         | 90000.00 |
| Carol White   | IT         | 80000.00 |
| David Brown   | Sales      | 75000.00 |
+---------------+------------+----------+

You can also filter the view further:

sqlCopy
1SELECT * FROM high_earners WHERE department = 'IT';

Example 3: Create a Complex View

sqlCopy
1CREATE VIEW employee_summary AS
2SELECT 
3    department,
4    COUNT(*) AS total_employees,
5    ROUND(AVG(salary), 2) AS avg_salary,
6    MAX(salary) AS highest_salary
7FROM employees
8GROUP BY department;

sqlCopy
1SELECT * FROM employee_summary;

Output:

+------------+-----------------+------------+----------------+
| department | total_employees | avg_salary | highest_salary |
+------------+-----------------+------------+----------------+
| HR         | 2               | 57500.00   | 60000.00       |
| IT         | 2               | 85000.00   | 90000.00       |
| Sales      | 1               | 75000.00   | 75000.00       |
+------------+-----------------+------------+----------------+

Example 4: Drop a View

sqlCopy
1DROP VIEW IF EXISTS high_earners;

Warning: Dropping a view doesn't affect the underlying table data. It only deletes the saved query definition.

Updatable vs. Non-Updatable Views

Updatable Views: You can run INSERT, UPDATE, or DELETE on the view, and it affects the underlying table.

Rules for updatable views:

• Must reference only one table
• Cannot contain GROUP BY, DISTINCT, aggregate functions (COUNT, SUM, etc.), or UNION
• Cannot contain derived columns (calculated expressions)

Example of an updatable view:

sqlCopy
1CREATE VIEW it_employees AS
2SELECT emp_id, emp_name, salary
3FROM employees
4WHERE department = 'IT';

Update through the view:

sqlCopy
1UPDATE it_employees SET salary = 95000 WHERE emp_id = 1;
2-- This updates the actual employees table!

Non-updatable view (contains GROUP BY):

sqlCopy
1CREATE VIEW dept_stats AS
2SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department;
3
4-- This will FAIL:
5UPDATE dept_stats SET emp_count = 10 WHERE department = 'IT';
6-- ERROR: The target table dept_stats of the update is not updatable

2️⃣ Triggers — Automatic Actions on Data Changes

Purpose: A trigger is a block of SQL that automatically executes before or after an INSERT, UPDATE, or DELETE operation.

Use cases:

• Audit logging (who changed what)
• Validating data before insertion
• Auto-updating related tables
• Enforcing complex business rules

Syntax:

sqlCopy
1CREATE TRIGGER trigger_name
2{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
3ON table_name
4FOR EACH ROW
5BEGIN
6    -- trigger body
7END;

Example 5: BEFORE INSERT Trigger

Scenario: Automatically set created_at when a new employee is inserted.

sqlCopy
1DELIMITER //
2
3CREATE TRIGGER before_employee_insert
4BEFORE INSERT ON employees
5FOR EACH ROW
6BEGIN
7    SET NEW.created_at = NOW();
8END //
9
10DELIMITER ;

Key concepts:

• DELIMITER // changes the statement terminator so ; inside the trigger body doesn't end the command prematurely
• NEW refers to the new row being inserted
• SET NEW.created_at = NOW() modifies the new row before it's saved

Testing:

sqlCopy
1INSERT INTO employees (emp_name, department, salary) 
2VALUES ('Frank Miller', 'IT', 85000);
3
4SELECT * FROM employees WHERE emp_name = 'Frank Miller';
5-- created_at is automatically filled

Example 6: AFTER INSERT Trigger — Audit Log

Scenario: Log every new employee insertion into audit_log.

sqlCopy
1DELIMITER //
2
3CREATE TRIGGER after_employee_insert
4AFTER INSERT ON employees
5FOR EACH ROW
6BEGIN
7    INSERT INTO audit_log (action, table_name, record_id, new_values)
8    VALUES (
9        'INSERT', 
10        'employees', 
11        NEW.emp_id, 
12        CONCAT('Name: ', NEW.emp_name, ', Dept: ', NEW.department, ', Salary: ', NEW.salary)
13    );
14END //
15
16DELIMITER ;

Testing:

sqlCopy
1INSERT INTO employees (emp_name, department, salary) 
2VALUES ('Grace Lee', 'Marketing', 65000);
3
4SELECT * FROM audit_log;

Output:

+--------+------------+-----------+-----------+-----------------------------+------------+---------------------+
| log_id | action     | table_name| record_id | old_values | new_values                  | changed_by | changed_at          |
+--------+------------+-----------+-----------+------------+-----------------------------+------------+---------------------+
| 1      | INSERT     | employees | 6         | NULL       | Name: Grace Lee, Dept: ...  | root@localhost| 2026-08-13 ... |
+--------+------------+-----------+-----------+------------+-----------------------------+------------+---------------------+

Example 7: AFTER UPDATE Trigger — Track Changes

Scenario: Log both old and new values when an employee's salary changes.

sqlCopy
1DELIMITER //
2
3CREATE TRIGGER after_employee_update
4AFTER UPDATE ON employees
5FOR EACH ROW
6BEGIN
7    INSERT INTO audit_log (action, table_name, record_id, old_values, new_values)
8    VALUES (
9        'UPDATE',
10        'employees',
11        NEW.emp_id,
12        CONCAT('Old Salary: ', OLD.salary),
13        CONCAT('New Salary: ', NEW.salary)
14    );
15END //
16
17DELIMITER ;

Key concepts:

• OLD refers to the row values before the update
• NEW refers to the row values after the update

Testing:

sqlCopy
1UPDATE employees SET salary = 70000 WHERE emp_id = 2;
2
3SELECT * FROM audit_log WHERE action = 'UPDATE';

Example 8: AFTER DELETE Trigger

Scenario: Log when an employee is deleted.

sqlCopy
1DELIMITER //
2
3CREATE TRIGGER after_employee_delete
4AFTER DELETE ON employees
5FOR EACH ROW
6BEGIN
7    INSERT INTO audit_log (action, table_name, record_id, old_values)
8    VALUES (
9        'DELETE',
10        'employees',
11        OLD.emp_id,
12        CONCAT('Deleted: ', OLD.emp_name, ' from ', OLD.department)
13    );
14END //
15
16DELIMITER ;

Testing:

sqlCopy
1DELETE FROM employees WHERE emp_id = 5;
2
3SELECT * FROM audit_log WHERE action = 'DELETE';

Example 9: Drop a Trigger

sqlCopy
1DROP TRIGGER IF EXISTS after_employee_insert;

3️⃣ Stored Procedures — Reusable SQL Programs

Purpose: A stored procedure is a saved collection of SQL statements that you can execute by calling its name. It can accept input parameters and return multiple result sets.

Benefits:

• Reuse complex logic without rewriting
• Improve performance (pre-compiled)
• Enhance security (users can execute procedures without direct table access)

Example 10: Simple Stored Procedure

Scenario: Get all employees from a specific department.

sqlCopy
1DELIMITER //
2
3CREATE PROCEDURE GetEmployeeByDept(IN dept_name VARCHAR(50))
4BEGIN
5    SELECT emp_id, emp_name, salary
6    FROM employees
7    WHERE department = dept_name;
8END //
9
10DELIMITER ;

Key concepts:

• IN means it's an input parameter
• dept_name is the parameter name
• VARCHAR(50) is the parameter type

Calling the procedure:

sqlCopy
1CALL GetEmployeeByDept('IT');

Output:

+--------+---------------+----------+
| emp_id | emp_name      | salary   |
+--------+---------------+----------+
| 1      | Alice Johnson | 90000.00 |
| 3      | Carol White   | 80000.00 |
| 6      | Frank Miller  | 85000.00 |
+--------+---------------+----------+

sqlCopy
1CALL GetEmployeeByDept('HR');

Example 11: Procedure with Multiple Parameters

sqlCopy
1DELIMITER //
2
3CREATE PROCEDURE GetEmployeesBySalaryRange(
4    IN min_salary DECIMAL(10,2),
5    IN max_salary DECIMAL(10,2)
6)
7BEGIN
8    SELECT emp_name, department, salary
9    FROM employees
10    WHERE salary BETWEEN min_salary AND max_salary
11    ORDER BY salary DESC;
12END //
13
14DELIMITER ;

Calling:

sqlCopy
1CALL GetEmployeesBySalaryRange(60000, 85000);

Example 12: Procedure with OUT Parameter

Scenario: Get the total salary expense as an output variable.

sqlCopy
1DELIMITER //
2
3CREATE PROCEDURE GetTotalSalary(OUT total DECIMAL(12,2))
4BEGIN
5    SELECT SUM(salary) INTO total FROM employees;
6END //
7
8DELIMITER ;

Calling:

sqlCopy
1CALL GetTotalSalary(@result);
2SELECT @result AS total_salary_expense;

4️⃣ Functions — Calculations That Return a Value

Purpose: A function is like a procedure, but it must return exactly one value. You can use it inside SELECT statements.

Difference from Procedures:

Example 13: Create a Function

Scenario: Calculate a 10% bonus based on salary.

sqlCopy
1DELIMITER //
2
3CREATE FUNCTION CalculateBonus(salary DECIMAL(10,2))
4RETURNS DECIMAL(10,2)
5DETERMINISTIC
6BEGIN
7    RETURN salary * 0.10;
8END //
9
10DELIMITER ;

Key concepts:

• RETURNS DECIMAL(10,2) declares the return type
• DETERMINISTIC means the function always returns the same result for the same input (required in some MySQL configurations)
• RETURN sends back the calculated value

Using the function in a query:

sqlCopy
1SELECT 
2    emp_name,
3    salary,
4    CalculateBonus(salary) AS bonus,
5    (salary + CalculateBonus(salary)) AS total_compensation
6FROM employees;

Output:

+---------------+----------+--------+-------------------+
| emp_name      | salary   | bonus  | total_compensation|
+---------------+----------+--------+-------------------+
| Alice Johnson | 90000.00 | 9000.00| 99000.00          |
| Bob Smith     | 55000.00 | 5500.00| 60500.00          |
| ...           | ...      | ...    | ...               |
+---------------+----------+--------+-------------------+

Example 14: Drop a Procedure or Function

sqlCopy
1DROP PROCEDURE IF EXISTS GetEmployeeByDept;
2DROP FUNCTION IF EXISTS CalculateBonus;

📝 Complete Command Reference

🚀 Hands-On Project: Audit Log System + Reporting Views

Project Goal

Build a complete audit system that automatically tracks all changes to employee data, plus create reusable reporting views for management.

Step 1: Create the Base Tables

sqlCopy
1CREATE DATABASE hr_system;
2USE hr_system;
3
4CREATE TABLE departments (
5    dept_id INT PRIMARY KEY AUTO_INCREMENT,
6    dept_name VARCHAR(50) NOT NULL
7);
8
9CREATE TABLE employees (
10    emp_id INT PRIMARY KEY AUTO_INCREMENT,
11    emp_name VARCHAR(100) NOT NULL,
12    email VARCHAR(100) UNIQUE,
13    department_id INT,
14    salary DECIMAL(10,2) CHECK (salary > 0),
15    status ENUM('Active', 'Inactive') DEFAULT 'Active',
16    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
17    FOREIGN KEY (department_id) REFERENCES departments(dept_id)
18);
19
20CREATE TABLE audit_log (
21    log_id INT PRIMARY KEY AUTO_INCREMENT,
22    action VARCHAR(20),
23    table_name VARCHAR(50),
24    record_id INT,
25    old_data TEXT,
26    new_data TEXT,
27    changed_by VARCHAR(100) DEFAULT CURRENT_USER(),
28    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
29);
30
31INSERT INTO departments (dept_name) VALUES ('IT'), ('HR'), ('Sales');
32
33INSERT INTO employees (emp_name, email, department_id, salary) VALUES
34('Alice Johnson', 'alice@company.com', 1, 90000),
35('Bob Smith', 'bob@company.com', 2, 55000),
36('Carol White', 'carol@company.com', 1, 80000);

Step 2: Create Triggers for Audit Logging

sqlCopy
1DELIMITER //
2
3-- Log new employee insertions
4CREATE TRIGGER trg_after_employee_insert
5AFTER INSERT ON employees
6FOR EACH ROW
7BEGIN
8    INSERT INTO audit_log (action, table_name, record_id, new_data)
9    VALUES (
10        'INSERT',
11        'employees',
12        NEW.emp_id,
13        CONCAT('Name: ', NEW.emp_name, ', Email: ', NEW.email, 
14               ', DeptID: ', NEW.department_id, ', Salary: ', NEW.salary)
15    );
16END //
17
18-- Log employee updates
19CREATE TRIGGER trg_after_employee_update
20AFTER UPDATE ON employees
21FOR EACH ROW
22BEGIN
23    INSERT INTO audit_log (action, table_name, record_id, old_data, new_data)
24    VALUES (
25        'UPDATE',
26        'employees',
27        NEW.emp_id,
28        CONCAT('Name: ', OLD.emp_name, ', Salary: ', OLD.salary, ', Status: ', OLD.status),
29        CONCAT('Name: ', NEW.emp_name, ', Salary: ', NEW.salary, ', Status: ', NEW.status)
30    );
31END //
32
33-- Log employee deletions
34CREATE TRIGGER trg_after_employee_delete
35AFTER DELETE ON employees
36FOR EACH ROW
37BEGIN
38    INSERT INTO audit_log (action, table_name, record_id, old_data)
39    VALUES (
40        'DELETE',
41        'employees',
42        OLD.emp_id,
43        CONCAT('Deleted Employee: ', OLD.emp_name, ' (ID: ', OLD.emp_id, ')')
44    );
45END //
46
47DELIMITER ;

Step 3: Create Reporting Views

sqlCopy
1-- View: Active employees with department names
2CREATE VIEW vw_active_employees AS
3SELECT 
4    e.emp_id,
5    e.emp_name,
6    e.email,
7    d.dept_name,
8    e.salary
9FROM employees e
10JOIN departments d ON e.department_id = d.dept_id
11WHERE e.status = 'Active';
12
13-- View: Department salary summary
14CREATE VIEW vw_department_summary AS
15SELECT 
16    d.dept_name,
17    COUNT(e.emp_id) AS employee_count,
18    ROUND(AVG(e.salary), 2) AS average_salary,
19    SUM(e.salary) AS total_payroll
20FROM departments d
21LEFT JOIN employees e ON d.dept_id = e.department_id
22GROUP BY d.dept_id, d.dept_name;
23
24-- View: Recent audit activity
25CREATE VIEW vw_recent_audit AS
26SELECT 
27    action,
28    table_name,
29    record_id,
30    changed_by,
31    changed_at
32FROM audit_log
33WHERE changed_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
34ORDER BY changed_at DESC;

Step 4: Create Stored Procedures

sqlCopy
1DELIMITER //
2
3-- Procedure: Get employees by department
4CREATE PROCEDURE sp_GetEmployeesByDept(IN p_dept_name VARCHAR(50))
5BEGIN
6    SELECT 
7        e.emp_name,
8        e.email,
9        e.salary,
10        d.dept_name
11    FROM employees e
12    JOIN departments d ON e.department_id = d.dept_id
13    WHERE d.dept_name = p_dept_name;
14END //
15
16-- Procedure: Deactivate employee (safe delete alternative)
17CREATE PROCEDURE sp_DeactivateEmployee(IN p_emp_id INT)
18BEGIN
19    UPDATE employees 
20    SET status = 'Inactive' 
21    WHERE emp_id = p_emp_id;
22END //
23
24DELIMITER ;

Step 5: Create a Helper Function

sqlCopy
1DELIMITER //
2
3CREATE FUNCTION fn_GetAnnualSalary(monthly_salary DECIMAL(10,2))
4RETURNS DECIMAL(12,2)
5DETERMINISTIC
6BEGIN
7    RETURN monthly_salary * 12;
8END //
9
10DELIMITER ;

Step 6: Test the Complete System

sqlCopy
1-- Test INSERT trigger
2INSERT INTO employees (emp_name, email, department_id, salary) 
3VALUES ('David Brown', 'david@company.com', 3, 75000);
4
5-- Test UPDATE trigger
6UPDATE employees SET salary = 95000 WHERE emp_id = 1;
7
8-- Test DELETE trigger
9DELETE FROM employees WHERE emp_id = 2;
10
11-- Check the audit log
12SELECT * FROM audit_log;
13
14-- Use the views
15SELECT * FROM vw_active_employees;
16SELECT * FROM vw_department_summary;
17
18-- Use the procedures
19CALL sp_GetEmployeesByDept('IT');
20CALL sp_DeactivateEmployee(3);
21
22-- Use the function
23SELECT emp_name, fn_GetAnnualSalary(salary) AS annual_salary FROM employees;

✅ Module 10 Summary

🎯 Practice Exercises

1. Create a view vw_high_earners showing employees with salary > $80,000.
2. Create a BEFORE INSERT trigger that automatically capitalizes the employee name.
3. Create a procedure sp_GetEmployeeById(IN p_id INT) that returns one employee.
4. Create a function fn_YearsOfService(hire_date DATE) that returns years worked.
5. Write an AFTER UPDATE trigger that prevents salary from being reduced by more than 20% (use SIGNAL SQLSTATE to raise an error).
6. Drop the vw_high_earners view and recreate it with an additional department filter.
7. Explain the difference between NEW and OLD in triggers.

🎓 What's Next?

In Module 11, you'll learn Transactions & Security (TCL & DCL) — how to group multiple operations into atomic transactions with COMMIT and ROLLBACK, and how to manage database users, roles, and privileges with GRANT and REVOKE! 🚀