Using Triggers Wisely and Sparingly

1. What a trigger is and when it makes sense

A trigger is procedural code that the database automatically executes as soon as a specific event occurs on a table, such as an INSERT, UPDATE, or DELETE. Unlike a CHECK constraint, which is limited to simple logical conditions, a trigger can contain arbitrary procedural logic: reading and writing other tables, computing values, raising exceptions. This power is exactly why a trigger should be used with care, because it acts implicitly, without an application code caller seeing it directly.

A trigger makes sense when a rule must hold regardless of the access path and cannot be expressed as a simple constraint. Typical use cases are audit trails that log every change, maintaining denormalized aggregate values across multiple tables, or enforcing invariants that span several rows or tables at once. A trigger is the wrong choice when it implements general business logic that would be better placed visibly in application code, such as shipment notifications or complex workflow decisions.

The rule of thumb for sparing use is: a trigger should be short, deterministic, and focused on exactly one task. As soon as a trigger starts taking on multiple business responsibilities, it becomes a black box that new team members must painstakingly discover, because reading the application code gives no hint of its existence. The following sections show concrete patterns where a trigger delivers the most value, and the pitfalls that otherwise turn it into a maintenance risk.

2. BEFORE vs AFTER triggers: differences and use cases

A BEFORE trigger runs before the database actually writes the row. It may still modify the NEW values of the row before they are persisted, making it the natural place for normalization and validation: converting an email address to lowercase, precomputing a derived value, or raising an exception to abort the entire write operation if a business rule is violated. A BEFORE trigger therefore prevents an invalid row from ever reaching the table.

An AFTER trigger runs after the row has already been written. It can no longer change the written values, but it is ideal for side effects that build on the already confirmed row: writing an audit log entry, updating a counter in a related table, or enqueueing a notification in a queue table. The deciding factor for choosing between BEFORE and AFTER is therefore whether the trigger still needs to influence the actual row, or whether it merely reacts to a change that has already taken place.

A common mistake is placing validation logic in an AFTER trigger and raising an exception there to roll back the write. This does work through an implicit transaction rollback, but it wastes computation on a write that is discarded anyway, and it unnecessarily complicates error handling. Validation belongs in a BEFORE trigger, side effects in an AFTER trigger.

3. Row level vs statement level triggers

A row level trigger fires once for every affected row of an INSERT, UPDATE, or DELETE. On a bulk update of ten thousand rows, that means ten thousand individual trigger executions, which noticeably adds to total runtime if the trigger logic itself is expensive. MySQL supports only row level triggers, while PostgreSQL and Oracle additionally offer statement level triggers that fire exactly once per statement, regardless of how many rows are affected.

Since version 10, PostgreSQL additionally allows transition tables via REFERENCING NEW TABLE AS ... OLD TABLE AS ..., letting a statement level trigger access the entire set of changed rows as a virtual table. This is considerably more efficient than ten thousand individual row level calls when the trigger logic aggregates over the whole set anyway, for example for a summary statistic after a bulk import. The choice between row level and statement level is therefore directly a performance decision, not just a syntactic variant.

-- PostgreSQL: statement-level trigger using a transition table,
-- fires once per statement instead of once per row
CREATE TABLE order_import_stats (
    import_id     BIGINT NOT NULL,
    rows_inserted INT NOT NULL,
    logged_at     TIMESTAMP NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION log_bulk_import_stats()
RETURNS TRIGGER AS $$
BEGIN
    -- new_rows behaves like a regular table for the whole batch
    INSERT INTO order_import_stats (import_id, rows_inserted)
    SELECT current_setting('app.import_id')::BIGINT, COUNT(*)
    FROM new_rows;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_bulk_stats
    AFTER INSERT ON orders
    REFERENCING NEW TABLE AS new_rows
    FOR EACH STATEMENT
    EXECUTE FUNCTION log_bulk_import_stats();

4. Building a clean audit trail with triggers

An audit trail is the most commonly justified use case for a trigger, because it must guarantee, regardless of access path, that every change is logged, whether it happens through the main application, an admin script, or a direct SQL client. An AFTER trigger on INSERT, UPDATE, and DELETE writes the old and new state of the row into a separate audit table, usually alongside a timestamp and the executing database user.

For storing the state, PostgreSQL's JSONB type combined with row_to_json or to_jsonb is a good fit, because it captures arbitrary column changes without the audit table needing to be adjusted every time the source table's schema changes. This pattern decouples the audit trail from the concrete schema and makes it robust against future column additions.

-- PostgreSQL: generic audit trail trigger, schema-agnostic via JSONB
CREATE TABLE audit_log (
    audit_id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    table_name  TEXT NOT NULL,
    operation   TEXT NOT NULL,
    old_values  JSONB,
    new_values  JSONB,
    changed_by  TEXT NOT NULL DEFAULT current_user,
    changed_at  TIMESTAMP NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION audit_row_change()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO audit_log (table_name, operation, old_values)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD));
        RETURN OLD;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO audit_log (table_name, operation, old_values, new_values)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;
    ELSE
        INSERT INTO audit_log (table_name, operation, new_values)
        VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_customer_audit
    AFTER INSERT OR UPDATE OR DELETE ON customer
    FOR EACH ROW EXECUTE FUNCTION audit_row_change();

5. Validation and normalization with BEFORE triggers

A BEFORE trigger is suited for rules that go beyond what a CHECK constraint can express, for example a check against another table or a computation that depends on several rows. One example is checking whether a discount code is still valid before an order line is written. A CHECK constraint cannot express this check because it may not access other tables, whereas a BEFORE trigger can.

Normalization is the second common use case: a BEFORE trigger can consistently lowercase an email address, trim leading and trailing whitespace, or precompute a derived value such as a search index field. Doing this normalization directly in the trigger ensures it applies regardless of whether the application, an import script, or a manually entered row writes it.

-- PostgreSQL: BEFORE trigger for normalization and cross-table validation
CREATE OR REPLACE FUNCTION validate_and_normalize_order()
RETURNS TRIGGER AS $$
DECLARE
    v_discount_valid BOOLEAN;
BEGIN
    -- normalize before the row is written
    NEW.customer_email := lower(trim(NEW.customer_email));

    -- cross-table check a CHECK constraint could never express
    IF NEW.discount_code IS NOT NULL THEN
        SELECT EXISTS (
            SELECT 1 FROM discount_code
            WHERE code = NEW.discount_code AND valid_until >= now()
        ) INTO v_discount_valid;

        IF NOT v_discount_valid THEN
            RAISE EXCEPTION 'Discount code % is invalid or expired', NEW.discount_code;
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_validate
    BEFORE INSERT OR UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION validate_and_normalize_order();

6. Trigger recursion and mutating table problems

A trigger that changes its own table, or a table on which another trigger is defined, can set off a cascade that in the worst case ends in an infinite loop. Oracle explicitly names this problem the "mutating table error" when a row level trigger tries to query the table currently being processed, while PostgreSQL and MySQL generally allow it, but can likewise run into uncontrolled recursion if a trigger on UPDATE changes the same table again via UPDATE.

The robust protection against these traps is an explicit recursion guard through a session variable, or in PostgreSQL through pg_trigger_depth(), which returns the current nesting depth of trigger execution. A trigger that checks whether it is already running inside another trigger call can deliberately skip its own logic and thereby prevent an infinite loop, without blocking cascading for the regular use case.

-- PostgreSQL: guard against unwanted trigger recursion using pg_trigger_depth()
CREATE OR REPLACE FUNCTION recalc_order_total()
RETURNS TRIGGER AS $$
BEGIN
    -- skip if this trigger call is itself nested inside another trigger
    IF pg_trigger_depth() > 1 THEN
        RETURN NEW;
    END IF;

    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(quantity * unit_price), 0)
        FROM order_line WHERE order_id = NEW.order_id
    )
    WHERE order_id = NEW.order_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_line_recalc
    AFTER INSERT OR UPDATE OR DELETE ON order_line
    FOR EACH ROW EXECUTE FUNCTION recalc_order_total();

7. Performance impact of triggers

A trigger costs computation time on every affected INSERT, UPDATE, or DELETE, and this cost factor multiplies with the number of affected rows for row level triggers. A bulk import of a million rows with an expensive row level trigger can extend runtime many times over compared to the same import without a trigger, especially if the trigger itself runs further queries against other tables.

For very large, controlled bulk operations where data quality is already guaranteed upfront, it is an established pattern to temporarily disable triggers, perform the import, and afterward recompute the values the trigger would normally maintain in a single batch. This approach must, however, be deliberate and documented, because in the meantime audit trail entries or aggregate values are missing until the follow up run completes.

-- PostgreSQL: disable triggers for a controlled bulk load, then recompute
ALTER TABLE order_line DISABLE TRIGGER trg_order_line_recalc;

COPY order_line (order_id, product_id, quantity, unit_price)
FROM '/data/bulk_import.csv' WITH (FORMAT csv);

ALTER TABLE order_line ENABLE TRIGGER trg_order_line_recalc;

-- Recompute affected aggregates once, in a single batch statement
UPDATE orders o
SET total_amount = (
    SELECT COALESCE(SUM(ol.quantity * ol.unit_price), 0)
    FROM order_line ol WHERE ol.order_id = o.order_id
)
WHERE o.order_id IN (SELECT DISTINCT order_id FROM order_line);

8. Debugging, maintainability, and naming conventions

The biggest practical drawback of a trigger is its invisibility in application code. A developer reading an UPDATE statement does not automatically see that this statement triggers a cascade effect across three further tables. This hidden coupling significantly complicates debugging, especially when a bug only surfaces days later as an unexplained data inconsistency. A consistent naming convention such as the prefix trg_, combined with complete documentation of every trigger in a central schema document, noticeably reduces this drawback.

Equally important is maintaining trigger definitions like regular code in version control, with migrations instead of manual changes made directly in the production database. A trigger that only exists in the head of a single developer becomes a maintenance trap once that person leaves the team. Following this discipline lets you benefit from the power of a trigger without sacrificing the traceability of the overall system.

9. Triggers vs alternatives: what to use when

Not every rule that could be implemented with a trigger should be implemented as one. The following overview shows which tool is the more robust and maintainable choice for which use case.

A trigger is the right choice exactly when a rule absolutely must be bound to the table itself, regardless of which process is writing. For anything that could just as reasonably be decided outside the database, application code is the more transparent and more easily testable alternative.

10. Summary

A trigger is a powerful tool for rules that must hold regardless of access path, such as audit trails or maintaining aggregate values across multiple tables. BEFORE triggers are suited for validation and normalization because they can still influence the row before it is written, AFTER triggers for side effects on already written data. Row level triggers fire per row, while statement level triggers with transition tables in PostgreSQL allow efficient bulk processing without thousands of individual calls.

Recursion protection through pg_trigger_depth(), deliberate deactivation during controlled bulk loads, and consistent naming conventions are the three most important measures for keeping a trigger performant and maintainable. Anyone following these criteria and using a trigger only where application code cannot reliably enforce the rule benefits from guaranteed consistency without losing the system's traceability.

11. FAQ: Using Triggers Wisely and Sparingly