Generate Test Data for Stored Procedures and Triggers
Generate Test Data for Stored Procedures and Triggers
Stored procedures and triggers sit at the intersection of business logic and data integrity. They enforce rules, cascade changes, and often hide the most fragile parts of a system. Yet many QA teams treat them as “black boxes” and feed them the same handful of rows that happen to exist in a shared dev database. The result: flaky tests, missed edge cases, and production bugs that surface only after a schema change.
This guide walks through a repeatable process for creating purpose‑built test data that exercises stored procedures and triggers thoroughly. You’ll see a decision framework, a worked scenario, validation checks, tool options (including a free generator you can try today), and a checklist you can hand to the team.
1. Why Test Data for SPs and Triggers Is Different
| Aspect | Typical Table‑Level Tests | Stored Procedure / Trigger Tests |
|---|---|---|
| Execution path | Direct INSERT/UPDATE/DELETE on a table | Implicit logic runs inside the engine (procedural code, cascade, timing) |
| State dependence | Often isolated to a single row | May depend on session variables, temporary tables, transaction nesting, or prior trigger firings |
| Side effects | Limited to the target table | Can touch audit logs, denormalized aggregates, external queues, or even other databases |
| Determinism | Easy to reset with TRUNCATE | Requires resetting sequence values, identity columns, and any runtime‑generated identifiers |
| Coverage goal | Row‑level constraints, PK/FK | Business rules, error handling, concurrency, rollback behavior |
Because the logic lives inside the database engine, the data you feed it must reproduce the exact pre‑conditions the code expects: specific column values, session settings, transaction isolation levels, and even the order of prior DML statements.
2. Common Pain Points
| Symptom | Root Cause | Typical Work‑around (and why it fails) |
|---|---|---|
| Tests pass locally but break in CI | Shared dev DB contains “lucky” rows that satisfy hidden pre‑conditions | Snapshotting the whole DB – heavy, slow, and still couples tests to unknown state |
| Trigger fires twice in production, once in test | Test data lacks the condition that suppresses the second fire (e.g., a flag column) | Adding a WHERE clause to the trigger – masks the real bug |
| Deadlocks appear only under load | Test data uses a single row, no contention | Running the same test in a loop – still single‑threaded, no lock escalation |
| Rollback logic never exercised | Test never forces an exception inside the procedure | Throwing a generic error in the test harness – doesn’t mimic the exact error path |
The pattern: test data does not reflect the real pre‑conditions that the procedural code evaluates.
3. Decision Criteria – Choosing a Generation Strategy
Before you write a single INSERT, decide how you will produce the data. The table below maps typical constraints to the most practical approach.
| Constraint | Recommended Strategy | When to Avoid |
|---|---|---|
| Schema changes frequently | Programmatic generation (scripts, ORM‑based factories) – version‑controlled with the code | Pure SQL scripts that hard‑code column lists |
| Large combinatorial space (e.g., 10+ nullable columns, enum values) | Combinatorial / pairwise generation (tools that emit orthogonal arrays) | Hand‑crafted CSV files – explode quickly |
| Need referential integrity across many tables | Model‑driven generation (define FK graph once, let engine resolve order) | Manual INSERT ordering – brittle |
| Must reproduce production‑like distributions | Statistical sampling / data masking from a sanitized prod dump | Synthetic random values – may miss skew |
| Team has limited DB admin rights | In‑process generation (run inside test transaction, roll back) | Requiring CREATE TABLE or BULK INSERT permissions |
| Trigger timing matters (BEFORE vs AFTER) | Explicit transaction scripts that control statement order | Bulk load tools that fire triggers in undefined order |
Rule of thumb: Start with a model‑driven, programmatic approach. It gives you version control, deterministic ordering, and the ability to inject edge‑case values programmatically. Add combinatorial or statistical layers only when the combinatorial explosion becomes a bottleneck.
4. End‑to‑End Workflow
1️⃣ Analyse the procedural code
2️⃣ Map pre‑conditions → data attributes
3️⃣ Define a data model (tables, FK graph, enums, defaults)
4️⃣ Choose generation technique (see decision table)
5️⃣ Implement a generator (script, factory, or tool)
6️⃣ Produce a *test data set* per test case (not a monolithic dump)
7️⃣ Load data inside a transaction, run the SP/trigger, assert
8️⃣ Capture side‑effects (audit tables, output params, messages)
9️⃣ Clean up (rollback or explicit delete) – keep CI fast
🔟 Store the data set as an artefact for regression debugging
Each step is lightweight; the heavy lifting lives in step 5. The rest is discipline.
5. Worked Example – Order Processing Procedure & Audit Trigger
5.1. Domain Sketch
-- Core tables
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
status VARCHAR(20) NOT NULL CHECK (status IN ('ACTIVE','SUSPENDED','CLOSED')),
created_at TIMESTAMP NOT NULL DEFAULT now()
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
total_amount NUMERIC(12,2) NOT NULL CHECK (total_amount >= 0),
currency CHAR(3) NOT NULL DEFAULT 'USD',
status VARCHAR(20) NOT NULL CHECK (status IN ('NEW','PAID','CANCELLED','REFUNDED')),
placed_at TIMESTAMP NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
id BIGINT PRIMARY KEY,
order_id BIGINT NOT NULL REFERENCES orders(id),
sku VARCHAR(50) NOT NULL,
qty INT NOT NULL CHECK (qty > 0),
unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0)
);
-- Audit log (populated by trigger)
CREATE TABLE order_audit (
id BIGSERIAL PRIMARY KEY,
order_id BIGINT NOT NULL,
changed_by VARCHAR(100) NOT NULL,
changed_at TIMESTAMP NOT NULL DEFAULT now(),
old_status VARCHAR(20),
new_status VARCHAR(20)
);
5.2. Stored Procedure – sp_place_order
CREATE OR REPLACE PROCEDURE sp_place_order(
p_customer_id BIGINT,
p_items JSONB, -- [{sku, qty, unit_price}, …]
p_currency CHAR(3) DEFAULT 'USD',
OUT p_order_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
v_total NUMERIC(12,2);
v_status VARCHAR(20) := 'NEW';
BEGIN
-- 1️⃣ Validate customer
IF NOT EXISTS (SELECT 1 FROM customers WHERE id = p_customer_id AND status = 'ACTIVE') THEN
RAISE EXCEPTION 'Customer not active';
END IF;
-- 2️⃣ Compute total
SELECT COALESCE(SUM((elem->>'qty')::int * (elem->>'unit_price')::numeric),0)
INTO v_total
FROM jsonb_array_elements(p_items) AS elem;
IF v_total <= 0 THEN
RAISE EXCEPTION 'Order total must be positive';
END IF;
-- 3️⃣ Insert order header
INSERT INTO orders (customer_id, total_amount, currency, status)
VALUES (p_customer_id, v_total, p_currency, v_status)
RETURNING id INTO p_order_id;
-- 4️⃣ Insert line items
INSERT INTO order_items (order_id, sku, qty, unit_price)
SELECT p_order_id,
(elem->>'sku')::varchar,
(elem->>'qty')::int,
(elem->>'unit_price')::numeric
FROM jsonb_array_elements(p_items) AS elem;
-- 5️⃣ Emit event (simplified)
PERFORM pg_notify('order_placed', p_order_id::text);
END;
$$;
5.3. Trigger – trg_order_status_audit
CREATE OR REPLACE FUNCTION fn_audit_order_status()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF OLD.status IS DISTINCT FROM NEW.status THEN
INSERT INTO order_audit (order_id, changed_by, old_status, new_status)
VALUES (NEW.id, current_user, OLD.status, NEW.status);
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trg_order_status_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW EXECUTE FUNCTION fn_audit_order_status();
5.4. Pre‑Condition Mapping
| Code Path | Required Pre‑Condition | Data Attribute(s) |
|---|---|---|
| Customer validation | customers.status = 'ACTIVE' | customers.status |
| Total > 0 | At least one item with qty>0 and unit_price>0 | order_items.qty, unit_price |
| Currency handling | orders.currency matches p_currency | orders.currency |
| Trigger fire | orders.status changes from any to different value | orders.status before/after |
| Notification | pg_notify called (observable only via LISTEN) | N/A – side‑effect |
5.5. Test‑Case Matrix
| TC‑ID | Goal | Customer | Items (JSON) | Expected Order Status | Trigger Audit Row? |
|---|---|---|---|---|---|
| TC‑01 | Happy path | ACTIVE | [{"sku":"A1","qty":2,"unit_price":10.00}] | NEW | Yes (NEW → NEW? no – only on change) |
| TC‑02 | Inactive customer | SUSPENDED | same as TC‑01 | exception | No |
| TC‑03 | Zero‑total order | ACTIVE | [{"sku":"B2","qty":0,"unit_price":5.00}] | exception | No |
| TC‑04 | Multi‑currency | ACTIVE | [{"sku":"C3","qty":1,"unit_price":100.00}] | NEW (currency EUR) | Yes (if later updated) |
| TC‑05 | Status transition | ACTIVE | same as TC‑01 | PAID (via separate UPDATE) | Yes (NEW → PAID) |
| TC‑06 | Concurrent updates (two sessions) | ACTIVE | same as TC‑01 | PAID / CANCELLED race | Both audit rows present |
Only TC‑01 and TC‑05 need the trigger to fire. The others verify that the procedure does not create audit rows inadvertently.
5.6. Generator Implementation (Python‑ish Pseudocode)
# test_data_gen.py – version‑controlled alongside the schema
from dataclasses import dataclass
from typing import List, Dict, Any
import json, uuid, random
@dataclass
class Customer:
id: int
email: str
status: str = "ACTIVE"
@dataclass
class OrderItem:
sku: str
qty: int
unit_price: float
def build_customer(status: str = "ACTIVE") -> Customer:
return Customer(
id=random.randint(10_000, 99_999),
email=f"cust_{uuid.uuid4().hex[:8]}@example.com",
status=status,
)
def build_items(*specs: Dict[str, Any]) -> List[OrderItem]:
return [OrderItem(**s) for s in specs]
def to_jsonb(items: List[OrderItem]) -> str:
return json.dumps([i.__dict__ for i in items])
# ---- Test case builders -------------------------------------------------
def tc_happy_path() -> Dict[str, Any]:
cust = build_customer("ACTIVE")
items = build_items({"sku": "A1", "qty": 2, "unit_price": 10.00})
return {
"customer": cust,
"proc_args": (cust.id, to_jsonb(items), "USD"),
"expect": {"order_status": "NEW", "audit_rows": 0},
}
def tc_inactive_customer() -> Dict[str, Any]:
cust = build_customer("SUSPENDED")
items = build_items({"sku": "A1", "qty": 1, "unit_price": 5.00})
return {
"customer": cust,
"proc_args": (cust.id, to_jsonb(items), "USD"),
"expect": {"exception": "Customer not active"},
}
# ... add builders for TC‑03 … TC‑06
Why this works
- Deterministic IDs – random but bounded, no clashes across parallel CI runs.
- Single source of truth – the same
build_customerused by unit tests, integration tests, and the data‑generator CLI. - Extensible – add a
build_orderhelper later for tests that start from an existing order (e.g., TC‑05).
5.7. Loading & Executing in a Test Harness
# pytest fixture – runs each test in its own transaction
@pytest.fixture
def db_txn(db_engine):
conn = db_engine.connect()
trans = conn.begin()
yield conn
trans.rollback() # guarantees clean state, no TRUNCATE needed
def test_tc_happy_path(db_txn):
data = tc_happy_path()
# 1️⃣ seed customer
db_txn.execute(
"INSERT INTO customers (id, email, status) VALUES (%s,%s,%s)",
(data["customer"].id, data["customer"].email, data["customer"].status)
)
# 2️⃣ call procedure
out = db_txn.execute(
"CALL sp_place_order(%s, %s, %s, %s)",
(*data["proc_args"], None) # last param is OUT
)
order_id = out.fetchone()[0]
# 3️⃣ assertions
row = db_txn.execute(
"SELECT status FROM orders WHERE id = %s", (order_id,)
).fetchone()
assert row[0] == "NEW"
audit = db_txn.execute(
"SELECT COUNT(*) FROM order_audit WHERE order_id = %s", (order_id,)
).fetchone()[0]
assert audit == 0 # trigger only fires on status change
The same fixture rolls back automatically, so CI stays fast and parallelizable.
6. Validation Checks – Did the Data Actually Exercise the Code?
| Check | How to Automate | What It Catches |
|---|---|---|
| Pre‑condition coverage | Parse procedure/trigger source → extract IF … THEN RAISE conditions → assert each branch has at least one test data set | Missing negative paths (inactive customer, zero total) |
| Trigger fire count | SELECT COUNT(*) FROM order_audit WHERE order_id = ? after each test | Silent trigger suppression, double‑fire bugs |
| Output parameter correctness | Compare OUT value vs. SELECT id FROM orders … | Procedure returning wrong identifier |
| Notification receipt | LISTEN order_placed; NOTIFY … in test harness | pg_notify not called or called with wrong payload |
| **Refer |
Read more
GraphQL Test Data Generation for Queries and Mutations
A practical guide to “GraphQL Test Data Generation for Queries and Mutations,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Postman Test Data Generation with CSV and JSON Files
A practical guide to “Postman Test Data Generation with CSV and JSON Files,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.
Generate API Test Data Directly from an OpenAPI Specification
A practical guide to “Generate API Test Data Directly from an OpenAPI Specification,” with worked scenarios, tool considerations, validation checks, and actionable advice for QA teams.