Triggers execute inside the transaction of the statement that fired them. What practical consequences does that have for error handling, locking, latency, and for calling an external system such as an HTTP API from trigger code?
answer
- same transaction, same thread, synchronous
- trigger error = caller's statement fails
- locks held to commit, not to trigger return
- hot aggregate row serializes writers
- no HTTP in triggers: write an outbox row
basics
~20 sEverything a trigger writes commits or rolls back with the caller's transaction, and an error in the trigger aborts the caller's statement. Trigger work extends the transaction, so locks are held longer and the caller waits for it. External calls cannot be rolled back, so they must not be made from a trigger.
solid answer
~60 s**Atomicity.** Trigger writes are part of the caller's transaction. That is the whole value proposition for auditing and invariants: a committed change cannot exist without its trigger's effects. **Errors.** Raising an error inside a trigger aborts the firing statement and surfaces to the client as that statement's failure. Depending on engine and settings the whole transaction may be marked for rollback. The client sees an error it never wrote code for, from a stack it cannot see. **Locking and latency.** Trigger work runs on the caller's thread and inside the caller's lock context. Every row the trigger touches stays locked until commit, and its execution time is added to the caller's response time. Two writers whose triggers touch the same aggregate row serialize on it — a hidden hot spot. **External calls.** They are not transactional. If the transaction later rolls back, the HTTP call already happened; if the call is slow or hangs, an open transaction holding locks hangs with it. Write an outbox row in the trigger and let a separate process do the call after commit.
code
sql · 10 linesCREATE FUNCTION notify_order_shipped() RETURNS trigger AS $$
BEGIN
-- NOT: perform an HTTP call here
INSERT INTO outbox (event_type, payload, created_at)
VALUES ('order.shipped',
json_build_object('order_id', NEW.id, 'shipped_at', NEW.shipped_at),
CURRENT_TIMESTAMP);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;go deeper
Say that trigger work is part of the same transaction, so it commits or rolls back together and a trigger error fails the original statement.
Add lock duration and latency: locks are held until commit and the caller waits for the trigger body.
Bring contention and diagnosis — hot aggregate rows, cross-trigger deadlocks, MVCC bloat from long transactions — and prescribe the outbox for external effects.
Set the policy: triggers may hold only short, deterministic, local work; anything crossing a system boundary goes through an outbox with idempotent delivery.
## The single fact everything follows from A trigger is not a background job. It executes synchronously, on the same session and thread as the statement that fired it, inside the same transaction, before that statement is reported complete to the client. Every consequence below is a restatement of that. ## Atomicity: the good part Because the trigger's writes share the caller's transaction, they share its fate. If the transaction commits, the audit row is there. If it rolls back — for any reason, including a failure three statements later — the audit row disappears with it. There is no window in which the base change is visible but the derived effect is missing. That property is exactly why triggers are chosen for audit trails and cross-table invariants: it cannot be achieved by application code that writes to a second system, and it cannot be achieved by anything asynchronous. ## Errors: the surprising part An error raised inside a trigger body propagates outward as a failure of the statement that fired it. The client's INSERT fails with a message produced by code the client never called. Whether just the statement or the whole transaction is doomed depends on the engine and its settings — some abort the transaction outright, some allow the client to catch the error and continue, and savepoints can bound the damage. Two practical implications: error messages from triggers are part of your API surface and should be written for the caller, not for the trigger author; and a trigger that can fail on ordinary data turns a working write path into an intermittent one. If a trigger performs a lookup that can miss, decide deliberately whether a miss is a rejection or a no-op. Note also that a failure partway through a multi-row statement rolls back the entire statement, including rows already processed and the trigger work done for them — statements are atomic. ## Locking, contention and duration Every row a trigger reads-for-update or writes acquires locks held until the transaction commits, not until the trigger returns. So triggers extend the *lock footprint* of ordinary application transactions into tables the application never named. Classic symptom: two unrelated inserts deadlock because their triggers touch the same two summary rows in opposite orders. A trigger that maintains a single aggregate row — "total orders today" — serializes every writer on that row. Throughput collapses to one transaction at a time on the hottest path, and the application team sees lock waits with no explanation in their code. The fix is usually to make the aggregate append-only (insert deltas, sum on read, compact periodically) rather than to update one row. Duration matters too: trigger time is added to the caller's latency and to the transaction's lifetime. On MVCC engines a longer transaction also holds back cleanup of old row versions, so slow triggers cause bloat far from the trigger itself. ## External side effects: the rule Do not call external systems from a trigger. Concretely: no HTTP calls, no sending mail, no writing to a message broker over the network, no touching the filesystem. Three reasons, each sufficient: 1. **Not rollback-able.** The transaction can still fail after the call. You have now told the outside world about a change that never happened, and there is no compensating action available inside the transaction. 2. **Availability coupling.** The external call's latency becomes the transaction's latency, and its outage becomes your database's outage — with open transactions holding locks while sockets time out. A slow third party can exhaust connections and stall unrelated writers. 3. **Retries duplicate.** If the statement is retried after a serialization failure, the trigger fires again, and the external call happens again. The correct pattern is the **transactional outbox**: the trigger inserts a row describing the intended external effect into a table, atomically with the change. A separate process reads committed outbox rows after commit and performs the call with its own retries and idempotency keys. You keep atomicity of the *decision* to notify while moving the *delivery* outside the transaction. Some engines can defer certain trigger checks to commit time (constraint triggers). That helps with ordering-sensitive validation; it does not make external calls safe, since commit can still fail. ## Practical guidance for trigger bodies - Keep them short and deterministic; no loops over large sets, no sleeps, no network. - Touch as few additional rows as possible, and touch them in a consistent order across all triggers to avoid deadlocks. - Prefer append-only writes over hot-row updates. - Make error messages actionable for the caller. - Assume the body may run more than once for the same logical operation, because of retries. ## The interview signal Start from "same transaction, same thread, synchronous", then derive atomicity, error propagation, extended lock footprint and added latency — and finish with the outbox as the named remedy for external effects. A candidate who reaches the hot-aggregate-row contention example is describing real production experience.
- A trigger maintains a single 'total orders today' row. What goes wrong under load, and how do you fix it?Every writing transaction updates the same row, so they serialize on that row's lock for the remainder of their transactions; throughput drops to one writer at a time and lock waits appear in code that never mentions the counter. The standard fix is to make the maintenance append-only — insert a delta row per change and aggregate on read, compacting periodically — or to shard the counter across several rows and sum them.
- If external calls are forbidden in triggers, how do you guarantee the notification happens exactly when the change commits?Use a transactional outbox: the trigger inserts an event row in the same transaction, so the intent to notify commits atomically with the change. A separate publisher reads committed outbox rows after commit and delivers with retries. Delivery becomes at-least-once, so consumers must be idempotent — exactly-once delivery is not available, but exactly-once recording is.
saying these in an interview costs you the question
- Believing trigger work commits independently of the firing transaction
- Making HTTP or email calls from trigger bodies
- Assuming locks taken by a trigger are released when the trigger body returns
- Ignoring that trigger execution time is added to the caller's latency
- Thinking a trigger error can be swallowed so the original statement still succeeds by default