Stored Procedures and Views — What Testers Should Know

So far this course has treated the database as a stack of tables you SELECT from, INSERT into, and UPDATE. Real production databases have more than tables — they have views (saved queries), stored procedures (saved SQL programs), and triggers (code that fires automatically on changes). You're unlikely to create these as a tester, but you'll meet them constantly — and not knowing they exist leads to confusing test failures and "why is this column changing on its own?" mysteries. This lesson maps the territory so you can navigate it.

The four database objects a tester encounters

Views — saved queries that look like tables

A view is a stored SELECT statement. You can SELECT from a view exactly as if it were a table; under the hood the database re-runs the underlying query against the live tables.

CREATE VIEW active_users AS
SELECT id, name, email, role
FROM users
WHERE is_active = TRUE;

After running that once, active_users is queryable like any other table:

SELECT * FROM active_users WHERE role = 'admin';

Result:

| id | name       | email             | role  |
|----|------------|-------------------|-------|
| 1  | Alice Khan | alice@example.com | admin |

Why testers care about views:

• They simplify complex queries you'd otherwise have to write yourself. Ask the dev team: "What views exist in this database?" The answer often saves hours of JOIN-writing.
• Views always reflect current base-table data — they're not snapshots. So if the underlying users table changes, active_users changes with it. (One exception: materialised views cache results until refreshed. Treat them differently — covered below.)
• They abstract away the schema. If the developers refactor users into two tables, the view can hide the change. Useful, if you remember the view exists.

A common production view in an e-commerce schema might look like:

CREATE VIEW order_summary AS
SELECT
    o.id,
    u.name        AS customer,
    o.total,
    o.status,
    COUNT(oi.id)  AS item_count
FROM orders o
JOIN users u       ON o.user_id   = u.id
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, u.name, o.total, o.status;

Now SELECT * FROM order_summary WHERE status = 'pending' gives you a denormalised, ready-for-display view of every pending order — without writing the JOIN yourself.

Materialised views — cached, not live

A materialised view stores the result of the query, not just the query text. Querying it is fast (it's reading from a snapshot), but the data may be stale until the view is refreshed.

-- PostgreSQL syntax
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT DATE(created_at) AS day, SUM(total) AS revenue
FROM orders
GROUP BY DATE(created_at);
 
-- Manual refresh:
REFRESH MATERIALIZED VIEW daily_revenue;

For QA: if a report shows yesterday's number even after data has changed, you might be looking at an unrefreshed materialised view. Knowing the difference saves you from filing a bug that's actually a refresh-policy concern.

Stored procedures — saved SQL programs

A stored procedure is a named block of SQL (often with control flow — IF, LOOP, variables) that lives inside the database. You call it by name with parameters.

-- Postgres-flavoured definition (syntax varies a lot per database)
CREATE PROCEDURE reset_test_data()
LANGUAGE SQL AS $$
    DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%@ci-test.local'));
    DELETE FROM orders     WHERE user_id IN (SELECT id FROM users WHERE email LIKE '%@ci-test.local');
    DELETE FROM users      WHERE email LIKE '%@ci-test.local';
$$;

Once defined, you call it with one statement:

-- MySQL / PostgreSQL / SQL Server (slightly different syntaxes)
CALL reset_test_data();
EXEC generate_test_users @count = 50;

Why testers care about stored procedures:

• The dev team may have built reset_test_data or seed_orders_for_user already. Use them — they're maintained and faster than writing your own.
• Stored procedures often encode business logic. If the application's discount rules live in apply_discount(order_id), your tests need to verify that procedure works correctly. The procedure is the contract.
• Some operations may only be available via stored procedures — admin-restricted ones, audit-required ones. Direct table access may be blocked.
• A failing test where the procedure was the cause is a different bug from a failing test where the application logic was the cause. Knowing which to investigate saves time.

A small dialect note worth keeping in mind: stored-procedure syntax is the most divergent area in SQL. PostgreSQL, MySQL, SQL Server, and Oracle each have their own dialects. The high-level concept — call a saved program by name — is universal; the exact syntax is per-database.

Triggers — code that runs without you asking

A trigger is SQL that fires automatically when a row is inserted, updated, or deleted. You don't call them; the database does, every time.

-- Conceptual example — auto-update updated_at on every UPDATE
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
SET NEW.updated_at = CURRENT_TIMESTAMP;

After that trigger exists, every UPDATE on orders causes updated_at to refresh — even if your UPDATE statement didn't mention it. As a tester, this is critical to know:

• "Why is updated_at moving when I didn't update it?" — a trigger.
• "Why did three audit_log rows appear after my single INSERT?" — a trigger.
• "Why does deleting a user also nuke their cart?" — could be ON DELETE CASCADE, could be a trigger.

You'll rarely write a trigger. But you'll encounter their effects, and if you don't know triggers exist, the effects look like haunted-database behaviour. Ask the dev team: "What triggers exist on these tables?" The list is usually short, and worth knowing.

How to enumerate the objects in your database

Most databases expose a system catalog you can query to discover what views, procedures, and triggers exist:

-- PostgreSQL
SELECT table_name FROM information_schema.views WHERE table_schema = 'public';
SELECT routine_name FROM information_schema.routines WHERE routine_schema = 'public';
SELECT trigger_name, event_object_table FROM information_schema.triggers;
 
-- MySQL
SHOW FULL TABLES WHERE Table_type = 'VIEW';
SHOW PROCEDURE STATUS WHERE Db = DATABASE();
SHOW TRIGGERS;
 
-- SQLite
SELECT name FROM sqlite_master WHERE type IN ('view', 'trigger');

Run those once on a new project and you'll have a useful map of the database before you write your first test.

When testing means testing the database object itself

Sometimes the test is of the procedure or trigger:

• Stored-procedure test: call it with various inputs, verify the data state afterwards. "Does apply_discount(1, 0.10) actually deduct 10% from the order's total?"
• Trigger test: UPDATE a row and verify the trigger's side-effect happened. "After UPDATE on orders, did an audit_log row appear?"
• View test: verify the view returns the rows it claims to. "Is active_users actually filtering on is_active = TRUE?"

Each of these tests is shaped exactly like the API/UI verification tests from Chapter 5 — perform an action, query the database, assert. The action just happens to be a CALL or an UPDATE instead of a POST.

⚠️ Common Mistakes

• Treating views as if they store data. A regular view is a saved query — every SELECT re-runs the underlying SQL. If a view is slow, the query inside it is slow. Materialised views cache; regular views don't.
• Ignoring triggers when explaining unexpected changes. A timestamp moves "by itself," an audit row appears, a related row disappears — triggers are a common cause. Ask, don't guess.
• Assuming stored procedures are pure SQL. Most procedure dialects have their own control flow (IF, LOOP, variables) and error handling. Reading and testing them benefits from a quick dialect read-up before you dive in.

🎯 Practice Task

20 minutes. Mostly exploration, not query-writing.

1. On your practice database, run the appropriate information_schema (or sqlite_master) query to list every view. In our seeded e-commerce database there are none — but the query is the one you'd run on a real project.
2. Create a simple view yourself: CREATE VIEW active_users AS SELECT id, name, email FROM users WHERE is_active = TRUE;. Then SELECT * FROM active_users;. Confirm it returns 4 rows (Carol is excluded).
3. UPDATE Carol to is_active = TRUE. Re-run SELECT * FROM active_users; — confirm Carol is now included. (This proves the view is live, not cached.) Then UPDATE her back.
4. Drop your view: DROP VIEW active_users;.
5. Read up on your database's stored-procedure syntax — just the first paragraph of the docs is enough. Write down: how do I call a stored procedure? What's the syntax for parameters?
6. Stretch: in your real workplace project, ask a developer to show you one production view, one stored procedure, and one trigger. Read through each. Note: which of these did the application's behaviour depend on, and could you have figured that out from a SELECT alone? That gap — the difference between "what the table looks like" and "what the database actually does" — is exactly what this lesson exists to close.

That's the end of the formal curriculum. Chapter 7 is your capstone — a project brief, a guided walkthrough, and a self-review. Bring the whole course to bear on a small, realistic verification task.