Views, Triggers & Stored Procedures

1. Views

A View is a virtual table based on the result set of a SQL query. It does not store data physically; it dynamically retrieves data from the underlying base tables every time it is queried.

CREATE VIEW HighEarners AS
SELECT name, salary, department
FROM Employees
WHERE salary > 100000;

-- Use it like a regular table
SELECT * FROM HighEarners WHERE department = 'Engineering';

Advantages: Simplifies complex queries, provides a security layer (expose only certain columns), and presents a consistent interface even if underlying tables change.

Updatable Views: Simple views (no JOINs, no aggregates, no DISTINCT) can support INSERT, UPDATE, and DELETE operations that are translated to the base tables.

2. Triggers

A Trigger is a stored procedure that automatically executes in response to certain events on a particular table (INSERT, UPDATE, DELETE).

CREATE TRIGGER audit_salary_change
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (emp_id, old_salary, new_salary, changed_at)
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END;

Use Cases: Maintaining audit trails, enforcing complex business rules, automatically updating derived data.

Caution: Overuse of triggers creates hidden side effects that make debugging extremely difficult. They execute implicitly and can cascade.

3. Stored Procedures

A Stored Procedure is a precompiled collection of SQL statements stored on the database server. Applications call the procedure by name instead of sending raw SQL queries.

CREATE PROCEDURE TransferFunds(
    IN from_id INT, IN to_id INT, IN amount DECIMAL
)
BEGIN
    START TRANSACTION;
    UPDATE Accounts SET balance = balance - amount WHERE id = from_id;
    UPDATE Accounts SET balance = balance + amount WHERE id = to_id;
    COMMIT;
END;

CALL TransferFunds(101, 202, 500.00);

Advantages: Reduced network traffic (one call instead of multiple SQL statements), precompiled for faster execution, centralized business logic on the server.