Triggers and Cursors

Triggers and cursors have the same reputation: powerful, overused and a frequent cause of mysteries. The reason is the same too. Both do things the person reading the query cannot see.

Use them deliberately, with a clear reason, and they are excellent. Use them by reflex and you spend a weekend wondering why a table changes by itself.

What a trigger is#

A trigger tells the database: whenever this kind of change happens on this table, run that function. It fires inside the same transaction as the change, so if the transaction rolls back, whatever the trigger did rolls back with it.

Every trigger is defined by three decisions.

Inside a row trigger the function sees two special records: NEW (the row being written) and OLD (the row as it was). INSERT has only NEW, DELETE only OLD, UPDATE both.

Example: an audit trail#

The business wants a record of every price change.

CREATE TABLE product_price_audit (
    audit_id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id INTEGER       NOT NULL,
    old_price  NUMERIC(10,2) NOT NULL,
    new_price  NUMERIC(10,2) NOT NULL,
    changed_at TIMESTAMPTZ   NOT NULL DEFAULT now(),
    changed_by TEXT          NOT NULL DEFAULT current_user
);

CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO product_price_audit (product_id, old_price, new_price)
    VALUES (OLD.product_id, OLD.unit_price, NEW.unit_price);
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_products_price_audit
AFTER UPDATE OF unit_price ON products
FOR EACH ROW
WHEN (OLD.unit_price IS DISTINCT FROM NEW.unit_price)
EXECUTE FUNCTION log_price_change();

A trigger function returns the special type TRIGGER. The trigger definition itself only names the event and points at the function.

Two refinements keep this cheap. UPDATE OF unit_price fires only when that column is in the SET list. The WHEN condition skips updates that write the same price back.

Try it:

UPDATE products SET unit_price = 95.00 WHERE product_id = 1;
SELECT product_id, old_price, new_price FROM product_price_audit;

 product_id | old_price | new_price
------------+-----------+-----------
          1 |     89.00 |     95.00

The application issued one UPDATE and knew nothing about the audit. That is the benefit, and also the risk. Anyone updating prices, from any script or console, is audited. And anyone reading only the UPDATE would never guess a second table was written.

BEFORE triggers can change the row#

A BEFORE row trigger returns the row that will actually be written. Modify NEW and return it, and you change what gets stored. The classic use is a timestamp column:

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON some_table
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Return rules:

• BEFORE ... FOR EACH ROW: return NEW (possibly modified) to proceed, or NULL to silently skip this row.
• AFTER ... FOR EACH ROW: the return value is ignored. Return NULL or NEW.
• To reject a change, RAISE EXCEPTION. The statement fails and rolls back.

Row versus statement triggers#

FOR EACH ROW runs once per affected row. An UPDATE touching 100,000 rows fires it 100,000 times. FOR EACH STATEMENT runs once for the whole statement, and with transition tables (REFERENCING NEW TABLE AS new_rows) it can see all changed rows at once. For bulk maintenance, a statement-level trigger is far cheaper.

TG_OP tells a shared function which event fired it ('INSERT', 'UPDATE', 'DELETE'), so one function can serve several events.

If several triggers exist for the same event, they run in alphabetical order by trigger name. That is the entire ordering mechanism, so name them deliberately (trg_10_..., trg_20_...) when order matters.

INSTEAD OF triggers#

Complex views are not updatable on their own. An INSTEAD OF trigger intercepts the write and does what you define, such as splitting it across the base tables. It is the supported way to give a join-view write behavior.

When not to use a trigger#

A trigger is the right answer less often than it looks. Compare these alternatives first.

Constraints are declarative and visible in \d. A trigger is imperative and invisible there.

Specific hazards:

• Hidden behavior. The table does things nobody can see from the query.
• Cascades. A trigger writes to table B, which has a trigger that writes to table A. Recursion and loops follow.
• Bulk performance. A million-row load fires a million function calls.
• Surprising rollbacks. An audit row written by a trigger disappears when the transaction rolls back. For an audit of attempts, a trigger is the wrong tool.
• Testing. Disabling triggers for a data load (ALTER TABLE ... DISABLE TRIGGER ...) is common and easy to forget to re-enable.

Cursors#

A cursor retrieves rows from a query a few at a time, instead of all at once. The query result stays on the server and you pull from it.

In plain SQL:

BEGIN;
DECLARE recent_orders CURSOR FOR
    SELECT order_id, order_date FROM orders ORDER BY order_id;

FETCH 3 FROM recent_orders;
FETCH NEXT FROM recent_orders;
CLOSE recent_orders;
COMMIT;

 order_id | order_date
----------+------------
        1 | 2024-02-01
        2 | 2024-03-15
        3 | 2024-02-10
(3 rows)

 order_id | order_date
----------+------------
        4 | 2024-02-20
(1 row)

A cursor lives inside a transaction unless declared WITH HOLD. It holds a position, so each FETCH continues where the last stopped.

Cursors in PL/pgSQL#

Inside procedural code the most common form is the implicit cursor of a FOR loop:

DO $$
DECLARE
    r       RECORD;
    running NUMERIC := 0;
BEGIN
    FOR r IN
        SELECT order_id, order_total(order_id) AS total
        FROM orders
        WHERE status = 'delivered'
        ORDER BY order_id
    LOOP
        running := running + r.total;
        RAISE NOTICE 'order %: total %, running %', r.order_id, r.total, running;
    END LOOP;
END;
$$;

NOTICE:  order 1: total 148.00, running 148.00
NOTICE:  order 2: total 334.00, running 482.00
NOTICE:  order 3: total 199.00, running 681.00
...

DO runs an anonymous block, handy for one-off scripts. The loop uses the order_total function from the previous lesson.

Row-by-row is the slow way#

That loop works. It is also the wrong tool for this job, because a window function does the same in one statement:

SELECT order_id, order_total(order_id) AS total,
       SUM(order_total(order_id)) OVER (ORDER BY order_id) AS running
FROM orders
WHERE status = 'delivered';

Set-based SQL lets the planner choose algorithms, use indexes and process rows in bulk. A cursor loop executes one statement per row with a context switch each time. On large tables the difference is minutes versus seconds.

The habit to build: whenever you reach for a loop, ask whether the work is a join, an aggregate, a window function or a single UPDATE ... FROM. Nine times out of ten it is.

When cursors do earn their place#

• Streaming huge results. Fetching 50 million rows into application memory crashes the app. A server-side cursor (a named cursor in most drivers) delivers them in batches of, say, 10,000.
• Calling something per row that cannot be set-based. Sending an email, calling an external API, running a procedure with side effects.
• Batching large maintenance. Processing a huge backlog in chunks, committing between chunks, to avoid one giant transaction.

Common mistakes#

Putting business logic in triggers. Six months later nobody remembers it exists.

Row-level triggers on bulk-loaded tables. The load grinds. Disable them for the load, or use statement-level triggers.

Trigger chains. One trigger causes another and the behavior becomes impossible to trace.

Returning nothing from a BEFORE row trigger. Forgetting RETURN NEW makes the function return NULL, and the write is silently skipped.

Looping over rows when one statement would do. Always try the set-based version first.

Practice#

1. Create the price audit trigger. Update three prices, then update one price to the same value, and confirm the audit table has three rows, not four.
2. Add an updated_at TIMESTAMPTZ column to a scratch table and wire up set_updated_at. Update a row and check the timestamp moves.
3. Write a BEFORE INSERT trigger on a scratch table that rejects any row where price < 0 with a clear error message. Then do the same with a CHECK constraint and compare the two for simplicity.
4. Use a cursor to fetch the first two rows from products ordered by price, then the next two.
5. Rewrite the running-total loop as a single query, and explain why it should scale better.