Stored Procedures, Triggers and Views

Why it matters

Database logic is easy to overlook. It sits outside the application code most reviews look at, changes through migration scripts, and a trigger acts silently on every path that touches its table: the API, an admin fix, a nightly import. A defect there damages data directly — a refund created twice, stock returned for a shipped order, a report that counts orders twice — and often surfaces weeks later, far from its cause.

These routines also hold critical rules: money, stock, status changes, audit records. Calling them directly and checking every table they touch lets you find such defects before release.

Stored procedures: operations inside the database

A stored procedure is created in the database, usually by a migration script, and called by name. It takes input (IN), output (OUT) or INOUT parameters and can contain IF branches, loops, several INSERT, UPDATE and DELETE statements, and explicit errors such as ORDER_NOT_CANCELLABLE. Teams use procedures to keep a multi-step operation next to the data, or to let the application run it without the right to change the tables directly.

A function is a close relative: it returns a value and is used inside a query, as in SELECT order_total(1042). A procedure is invoked on its own with CALL (PostgreSQL, MySQL) or EXEC (SQL Server); in PostgreSQL only a procedure may commit or roll back inside its body. When you read either, list:

• The parameters and their allowed values: inputs for equivalence partitioning and boundary values.
• Every branch (IF, CASE, early exits): each one needs a test.
• Every table read and written, including through triggers.
• Every error raised, and whether a failure halfway undoes what already ran.

-- PostgreSQL and MySQL: call a procedure
CALL cancel_order(1042, 7);

-- SQL Server (T-SQL)
EXEC dbo.cancel_order @order_id = 1042, @user_id = 7;

-- PostgreSQL and MySQL: a function returns a value and is used inside a query
SELECT order_total(1042);

Try it: SQL Formatter

Triggers: code that runs by itself

A trigger belongs to a table and fires on INSERT, UPDATE or DELETE. A BEFORE trigger runs before the row is written and can change or reject it; an AFTER trigger runs afterwards and suits side effects such as audit rows. A row-level trigger (FOR EACH ROW) runs once per changed row and can read the OLD and NEW values; a statement-level trigger runs once per statement. SQL Server has no BEFORE triggers, only AFTER and INSTEAD OF; its triggers run once per statement and see all changed rows in the inserted and deleted tables, so code that assumes one row breaks on a bulk update.

In a transactional engine, an error inside a trigger fails the statement that fired it and rolls back its changes. Triggers also skip some paths: TRUNCATE fires no DELETE triggers in PostgreSQL, MySQL or SQL Server, and in MySQL foreign key cascades fire no triggers at all. In PostgreSQL a trigger has two parts, as below: a function that RETURNS trigger and a CREATE TRIGGER statement that attaches it to the table; its WHEN condition uses IS DISTINCT FROM, a not-equal comparison that also handles NULL. For this audit trigger, test that it:

• Fires on every path that changes the table: API, admin screen, bulk script, import.
• Writes no row when a status is set to the value it already has.
• Fires once per changed row, also when one statement changes many rows.
• Records the person who made the change: on a shared application connection the database user is the application’s account, so the user must be passed in, as updated_by is here.

-- PostgreSQL: one audit row whenever an order's status really changes
CREATE FUNCTION log_status_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO order_audit (order_id, old_status, new_status, changed_by, changed_at)
  VALUES (NEW.order_id, OLD.status, NEW.status, NEW.updated_by, now());
  RETURN NEW;
END $$;

CREATE TRIGGER orders_status_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION log_status_change();

Views: saved queries that reports trust

A view stores a query, not data: each time it is read, the database runs the query against the current base tables. Views give reports and APIs a stable shape and can expose only some columns, such as customer data without phone numbers. A materialized view stores its result and shows the data as of its last refresh, so its tests also cover the refresh.

A view’s defects are query defects: a wrong join type drops rows, a join to a child table multiplies them, a filter is missing, NULLs vanish from counts. The view below joins order_items, so an order with two items is counted twice.

• Compare the view with an independent query on the base tables: your test oracle.
• Seed edge rows: an order with several items, a cancelled order, a day without orders, a NULL amount.
• Check the columns too: hidden personal data must not reappear under another name.
• For a materialized view, compare the result before and after the refresh.

CREATE VIEW daily_sales AS
SELECT o.order_date, COUNT(*) AS order_count, SUM(o.total) AS revenue
  FROM orders o
  JOIN order_items i ON i.order_id = o.order_id
 WHERE o.status <> 'CANCELLED'
 GROUP BY o.order_date;

-- Seed: one order on 2026-09-14, total 190.00, with two item rows
-- Expected: order_count = 1, revenue = 190.00
-- Actual:   order_count = 2, revenue = 380.00

Try it: SQL Query Validator

Testing stored logic: a worked example

Derive tests from each routine’s contract: partitions and boundary values for parameters, a decision table where conditions combine. Calling a procedure directly is component testing in ISTQB terms; calling it through the API that uses it also tests the integration. Do both: the application may pass other values or hide a clear database error behind a confusing one.

Each test is arrange, act, assert in a test database: seed known rows, make the call, then query every table that should change and every table that must not. If the procedure does not commit by itself, ending with ROLLBACK restores the starting state; otherwise use a database you can reset. Frameworks such as pgTAP (PostgreSQL) and tSQLt (SQL Server) automate this pattern.

Requirement: "An order can be cancelled only while PENDING or PAID. Cancelling sets the status to CANCELLED and returns every item to stock; a PAID order also gets one refund of its total. Otherwise the call raises ORDER_NOT_CANCELLABLE (or ORDER_NOT_FOUND) and nothing changes."

• PAID order 1042 (2 × product 11, 1 × product 12, total 190.00): CANCELLED, stock 50 → 52 and 8 → 9, one refund of 190.00, one audit row PAID → CANCELLED by user 7.
• PENDING order: cancelled, stock returned, one audit row PENDING → CANCELLED, no refund row.
• SHIPPED order 1043, a second cancel of the same order, unknown order 9999: an error, all four tables unchanged, stock never returned twice.
• Two lines for the same product, if the schema allows it: stock rises by both quantities. A procedure that returns stock with one PostgreSQL UPDATE … FROM order_items, without first summing the quantities per product, would apply only one of the two lines, because a target row that matches several joined rows is updated once.
• Failure halfway: if refunds.order_id has a unique key, seed a refund for order 1042 so that the last step, the refund insert, is rejected, and call without a wrapping transaction, as the application does. Status, stock and audit must stay unchanged.
• Through the API: the same rows, and a clear 4xx for the shipped order, not a 500 with a raw database error.

BEGIN;
-- Arrange: known rows (customer 317 exists; other columns use defaults)
INSERT INTO products (product_id, stock) VALUES (11, 50), (12, 8);
INSERT INTO orders (order_id, customer_id, status, total)
VALUES (1042, 317, 'PAID', 190.00);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1042, 11, 2), (1042, 12, 1);

-- Act
CALL cancel_order(1042, 7);

-- Assert: every table the procedure and its trigger touch
SELECT status FROM orders WHERE order_id = 1042;             -- CANCELLED
SELECT product_id, stock FROM products ORDER BY product_id;  -- 11: 52, 12: 9
SELECT amount FROM refunds WHERE order_id = 1042;            -- 1 row: 190.00
SELECT old_status, new_status, changed_by
  FROM order_audit WHERE order_id = 1042;                    -- 1 row: PAID, CANCELLED, 7
ROLLBACK;

Try it: SQL INSERT Generator Try it: Decision Table Generator

Frequently asked questions

References

• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupLexical structure of SQL in PostgreSQL: identifiers, quoting, string constants and escaping.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• ISTQB Certified Tester Foundation Level (CTFL) v4.0ISTQB · 2023The foundation syllabus of software testing: test levels and types, test design techniques, test management and tools.
• Transactions — PostgreSQL DocumentationPostgreSQL Global Development GroupWhat a transaction is: several steps applied all-or-nothing with BEGIN, COMMIT and ROLLBACK, invisible to others until committed.