Functions, procedures and triggers
Should this trigger ship?
The idea in 30 seconds
Triggers that keep a running total in another table look neat and fail in three quiet ways: every write for the same parent row now queues behind the others, the total drifts as soon as a change path is missed, and nobody reading the application can see any of it. Ask whether the number can be computed, or maintained somewhere less contended, before approving one.
Helps to know first: SQL data modification, Constraints and data integrity
On this page
The situation
The customer page shows each customer’s lifetime spend, computed by summing all their order items. An AI assistant opened a pull request to make it instant.
Review taskKeep total_spent up to date with a trigger
Pull request: “Store each customer’s lifetime spend and keep it current with a trigger, so the customer page doesn’t have to sum orders.”
ALTER TABLE customers ADD COLUMN total_spent numeric(12,2) NOT NULL DEFAULT 0;
CREATE FUNCTION add_to_total_spent() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
UPDATE customers SET total_spent = total_spent + NEW.quantity * NEW.unit_price
WHERE id = (SELECT customer_id FROM orders WHERE id = NEW.order_id);
RETURN NULL;
END $$;
CREATE TRIGGER order_items_total_spent
AFTER INSERT ON order_items
FOR EACH ROW EXECUTE FUNCTION add_to_total_spent();Would you approve it? Write the review you would leave.
Your call
Model review
Request changes. It makes one page faster by making every checkout slower and the number untrustworthy.
1. Checkouts for the same customer now wait for each other. The trigger updates the customer’s row, and only one transaction can update a row at a time. We ran two checkouts for the same customer, on different orders and different products: the second was blocked on the customers row until the first committed. With a one-second lock timeout it failed outright:
ERROR: canceling statement due to lock timeout
CONTEXT: while updating tuple (0,9) in relation "customers"For a business account placing many orders at once, every checkout now runs one at a time.
2. The total is already wrong after one delete. The trigger only runs on INSERT. Removing an item from an order, or changing a quantity, leaves total_spent untouched. After deleting one item we measured:
| Asha’s lifetime spend | |
|---|---|
Stored total_spent | 27,697.00 |
| Actual sum of her order items | 23,197.00 |
The same happens for cancelled orders, refunds, and any bulk fix run with triggers disabled.
3. The behaviour is invisible. Someone reading the checkout code sees an INSERT into order_items, not an update to customers. When checkouts slow down or the number is wrong, the cause is in a place nobody looks.
What I would ask for instead: first measure the sum. With an index on orders (customer_id), summing one customer’s items is a small query. If it really is too slow, compute it in the background (a materialized view or a scheduled job) and show when it was last updated, or keep a separate per-order totals table that never makes two checkouts touch the same row.
Compare it with yours. Did your review catch the main problems?
When a trigger is the right tool
Prefer a constraint for rules about a single row or a reference between tables. It is declared, visible in the schema, and fast.
Prefer a trigger when the rule must hold however the data is changed: for every client, every script, every manual fix. updated_at timestamps and audit trails are the classic cases.
Avoid triggers that update a shared row such as a counter or a running total on a parent. They turn independent writes into a queue.
Cover every event. A trigger for derived data needs INSERT, UPDATE and DELETE versions, and a plan for bulk changes.
Make it discoverable. Name triggers after what they do, and mention them where the application writes to that table.
Check yourself
Why did the second checkout wait when it was for a different order and different products?
Show answer
Both inserts fired the trigger, and both triggers updated the same
customersrow. Only one transaction can update a row at a time, so the second waited for the first to commit.Name two ways
total_spentdrifts from the truth with this trigger.Show answer
Any two of: deleting an order item, changing a quantity or price, cancelling or refunding an order, or bulk changes made with triggers disabled. The trigger only handles inserts.
What would you check before accepting any cached total?
Show answer
Whether computing it directly is actually too slow, with the right index; and, if a cache is needed, how stale it may be, how it is kept correct for every kind of change, and whether it makes concurrent writes contend for the same row.
Finished reading?
Saved in this browser only.
Sources
Where each claim on this page comes from.
- 1.PostgreSQL documentation: 13.2 Transaction IsolationPrimary· §13.2.1 Read Committed Isolation Level
A transaction trying to update a row another transaction is updating waits until the first commits or rolls back.
- 2.PostgreSQL documentation: 37.1 Overview of Trigger BehaviorPrimary· §37.1 Overview of Trigger Behavior
Triggers fire separately for INSERT, UPDATE and DELETE; a trigger defined for one event does not run for the others.
Go further
What to read next, and why it is worth the time.
- 1.PostgreSQL documentation: 13.3 Explicit LockingPrimary· §13.3.2 Row-Level Locks
The row locks behind the waiting checkout, and why updates to the same row always queue.
Applies to PostgreSQL 18. Draft: not yet fact-checked end to end.