Priya creates windows into the data

Views, triggers — saving complex queries and automating audit logging

⏱ 13 min 6 commands 5 takeaways

👁️

In this chapter

Priya

DBA, simplifying queries for the team

The story

Priya's next task: the marketing team needs a view of active paid users with their last order date and total spend. The data analyst needs the same view. The developer building the dashboard needs the same view.

She could give everyone the same JOIN query. But what if the business logic changes? She would have to update it in 5 places.

She created a VIEW instead.

WHAT IS A VIEW

A view is a saved SELECT query that looks like a table. When you query a view, MySQL runs the SELECT behind it and returns the result. The data is not duplicated -- the view is just a window into the real tables.

-- Without a view, everyone writes this:
SELECT
    u.id, u.name, u.email, u.city,
    MAX(o.ordered_at) AS last_order,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.name, u.email, u.city;

-- With a view, they write this:
SELECT * FROM vw_active_user_stats WHERE city = 'Mumbai';

CREATING A VIEW

CREATE OR REPLACE VIEW vw_active_user_stats AS
SELECT
    u.id,
    u.name,
    u.email,
    u.city,
    MAX(o.ordered_at) AS last_order,
    COUNT(o.id)       AS order_count,
    COALESCE(SUM(CASE WHEN o.status = 'paid' THEN o.amount ELSE 0 END), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.name, u.email, u.city;

-- Now query it like a table:
SELECT * FROM vw_active_user_stats ORDER BY total_spent DESC LIMIT 10;
SELECT * FROM vw_active_user_stats WHERE city = 'Mumbai';
SELECT city, SUM(total_spent) FROM vw_active_user_stats GROUP BY city;

USEFUL VIEWS FOR EVERY APP

-- View: pending orders with customer name:
CREATE OR REPLACE VIEW vw_pending_orders AS
SELECT
    o.id,
    u.name   AS customer_name,
    u.email  AS customer_email,
    o.product,
    o.amount,
    o.ordered_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.ordered_at ASC;

-- View: daily revenue summary:
CREATE OR REPLACE VIEW vw_daily_revenue AS
SELECT
    DATE(ordered_at)   AS day,
    COUNT(*)           AS orders,
    SUM(amount)        AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY DATE(ordered_at)
ORDER BY day DESC;

MANAGING VIEWS

SHOW FULL TABLES WHERE Table_type = 'VIEW';    -- list all views
SHOW CREATE VIEW vw_active_user_stats;          -- see the view definition
DROP VIEW IF EXISTS vw_active_user_stats;       -- delete a view

TRIGGERS -- AUTOMATIC ACTIONS ON DATA CHANGES

A trigger fires automatically when INSERT, UPDATE, or DELETE happens on a table.

-- Log every order status change automatically:
CREATE TABLE order_status_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    order_id   INT,
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by VARCHAR(50)
);

DELIMITER //

CREATE TRIGGER trg_order_status_change
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_status_log (order_id, old_status, new_status, changed_by)
        VALUES (NEW.id, OLD.status, NEW.status, USER());
    END IF;
END //

DELIMITER ;

-- Now every status change is logged automatically:
UPDATE orders SET status = 'paid' WHERE id = 10;
SELECT * FROM order_status_log;

SHOW TRIGGERS FROM myapp_db;     -- list all triggers
DROP TRIGGER trg_order_status_change;

Priya created 6 views that the whole team uses. When the business logic changed, she updated the view once. Everyone who queries it gets the updated logic immediately.

Key takeaways

A VIEW is a saved SELECT query that behaves like a table — update the view definition and everyone who queries it gets the new logic

CREATE OR REPLACE VIEW lets you update a view without dropping it first — safe for views in use

Triggers fire automatically on INSERT, UPDATE, or DELETE — ideal for audit logs without changing application code

OLD.column is the value before the change, NEW.column is the value after — use both in AFTER UPDATE triggers

SHOW FULL TABLES WHERE Table_type = 'VIEW' lists all views — they appear alongside tables but work differently

Commands from this chapter

$ CREATE OR REPLACE VIEW vw_user_stats AS SELECT ...;

Create or update a view

$ SELECT * FROM vw_active_user_stats WHERE city = 'Mumbai';

Query a view like a table

$ SHOW FULL TABLES WHERE Table_type = 'VIEW';

List all views in the database

$ SHOW CREATE VIEW vw_active_user_stats;

See the SELECT behind a view

$ CREATE TRIGGER trg_name AFTER UPDATE ON orders FOR EACH ROW BEGIN ... END

Create a trigger

$ SHOW TRIGGERS FROM myapp_db;

List all triggers