Quality is not optional. It's our standard. Free QA tools for testers and developers.

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

AspectTypical Table‑Level TestsStored Procedure / Trigger Tests
Execution pathDirect INSERT/UPDATE/DELETE on a tableImplicit logic runs inside the engine (procedural code, cascade, timing)
State dependenceOften isolated to a single rowMay depend on session variables, temporary tables, transaction nesting, or prior trigger firings
Side effectsLimited to the target tableCan touch audit logs, denormalized aggregates, external queues, or even other databases
DeterminismEasy to reset with TRUNCATERequires resetting sequence values, identity columns, and any runtime‑generated identifiers
Coverage goalRow‑level constraints, PK/FKBusiness 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

SymptomRoot CauseTypical Work‑around (and why it fails)
Tests pass locally but break in CIShared dev DB contains “lucky” rows that satisfy hidden pre‑conditionsSnapshotting the whole DB – heavy, slow, and still couples tests to unknown state
Trigger fires twice in production, once in testTest 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 loadTest data uses a single row, no contentionRunning the same test in a loop – still single‑threaded, no lock escalation
Rollback logic never exercisedTest never forces an exception inside the procedureThrowing 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.

ConstraintRecommended StrategyWhen to Avoid
Schema changes frequentlyProgrammatic generation (scripts, ORM‑based factories) – version‑controlled with the codePure 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 tablesModel‑driven generation (define FK graph once, let engine resolve order)Manual INSERT ordering – brittle
Must reproduce production‑like distributionsStatistical sampling / data masking from a sanitized prod dumpSynthetic random values – may miss skew
Team has limited DB admin rightsIn‑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 orderBulk 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 PathRequired Pre‑ConditionData Attribute(s)
Customer validationcustomers.status = 'ACTIVE'customers.status
Total > 0At least one item with qty>0 and unit_price>0order_items.qty, unit_price
Currency handlingorders.currency matches p_currencyorders.currency
Trigger fireorders.status changes from any to different valueorders.status before/after
Notificationpg_notify called (observable only via LISTEN)N/A – side‑effect

5.5. Test‑Case Matrix

TC‑IDGoalCustomerItems (JSON)Expected Order StatusTrigger Audit Row?
TC‑01Happy pathACTIVE[{"sku":"A1","qty":2,"unit_price":10.00}]NEWYes (NEW → NEW? no – only on change)
TC‑02Inactive customerSUSPENDEDsame as TC‑01exceptionNo
TC‑03Zero‑total orderACTIVE[{"sku":"B2","qty":0,"unit_price":5.00}]exceptionNo
TC‑04Multi‑currencyACTIVE[{"sku":"C3","qty":1,"unit_price":100.00}]NEW (currency EUR)Yes (if later updated)
TC‑05Status transitionACTIVEsame as TC‑01PAID (via separate UPDATE)Yes (NEW → PAID)
TC‑06Concurrent updates (two sessions)ACTIVEsame as TC‑01PAID / CANCELLED raceBoth 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_customer used by unit tests, integration tests, and the data‑generator CLI.
  • Extensible – add a build_order helper 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?

CheckHow to AutomateWhat It Catches
Pre‑condition coverageParse procedure/trigger source → extract IF … THEN RAISE conditions → assert each branch has at least one test data setMissing negative paths (inactive customer, zero total)
Trigger fire countSELECT COUNT(*) FROM order_audit WHERE order_id = ? after each testSilent trigger suppression, double‑fire bugs
Output parameter correctnessCompare OUT value vs. SELECT id FROM orders …Procedure returning wrong identifier
Notification receiptLISTEN order_placed; NOTIFY … in test harnesspg_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.