In a relational database, what is the difference between a BEFORE trigger and an AFTER trigger on an INSERT or UPDATE, and what can each one do that the other cannot?
answer
- BEFORE = shape the row; AFTER = react to it
- row image mutable only in BEFORE
- generated id visible only in AFTER
- BEFORE returning null can skip the row
- SQL Server: no BEFORE, use INSTEAD OF
basics
~20 sA BEFORE trigger runs before the row change is applied and can still modify the incoming row or reject the statement. An AFTER trigger runs once the change is applied, so it sees final values including generated keys, but changes it makes to the row image are ignored.
solid answer
~60 s**BEFORE** fires before the engine writes the row. That is why it is the only place you can *change what gets stored* — normalizing a value, stamping a timestamp, filling a derived column — by modifying the new row image. It is also the cheapest place to reject bad input, because nothing has been written yet. But it runs before constraints and generated values are finalized, so it cannot rely on the final stored state and cannot see a key assigned during the write. **AFTER** fires once the row change has been applied and immediate constraints checked. It sees the authoritative final values, including auto-generated identifiers, so it is the right place for effects that depend on the change having really happened: audit rows, maintaining a denormalized total, enqueuing work. Assigning to the row image there has no effect — the row is already written. Both run inside the invoking transaction, so raising an error in either aborts the statement and rolls the change back. Note that SQL Server has no BEFORE timing; its equivalents are AFTER and INSTEAD OF.
code
sql · 9 lines-- BEFORE: still able to change what gets stored
CREATE TRIGGER trg_orders_stamp
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION set_updated_at(); -- sets NEW.updated_at
-- AFTER: the generated order id now exists
CREATE TRIGGER trg_orders_audit
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION write_audit_row(); -- reads NEW.idgo deeper
State the rule plainly: BEFORE can still change or reject the row, AFTER sees the final stored values.
Place both in the write sequence relative to defaults, generated keys and constraint checks, and note SQL Server has no BEFORE.
Add the operational angle: rejecting in BEFORE avoids wasted writes, and both timings sit inside the caller's transaction so external side effects are unsafe in either.
Discuss when row shaping belongs in a declarative default or generated column instead of any trigger, and the maintenance cost of hidden BEFORE mutations.
## The sequence a row change goes through For one row being inserted or updated, a rough ordering holds across engines: 1. BEFORE row triggers run, in the engine's chosen order. 2. The row is written to the table, defaults and generated values are materialized, indexes updated. 3. Constraints are checked (NOT NULL, CHECK, unique, foreign key — some at statement end, some deferrable to commit). 4. AFTER row triggers run. Almost everything about BEFORE versus AFTER falls out of where they sit in that list. ## What BEFORE can do uniquely Because the row has not been stored, a BEFORE row trigger receives a *mutable* image of the incoming row and can change it. Typical legitimate uses: - normalizing input (lower-casing an email, trimming whitespace); - stamping created-at / updated-at columns; - computing a derived column from other columns; - validating and rejecting early with a clear, domain-specific error. In engines that give BEFORE triggers a return value, returning the row proceeds with it and returning a null value **skips that row entirely** — the write silently does not happen, which is powerful and, if undocumented, deeply confusing to whoever debugs the missing row later. BEFORE is also where you avoid work: rejecting a row before it is written costs no index maintenance and no undo. ## What AFTER can do uniquely AFTER sees the change as it truly landed. That matters for: - **Generated keys.** An identity or sequence-backed primary key is materialized during the write. A BEFORE trigger on INSERT may not have it; an AFTER trigger does. If your audit row or child row needs the id, it must be written in AFTER. - **Post-constraint truth.** By AFTER time, immediate constraints have passed, so you are not reacting to a change that is about to be rejected. - **Effects on other tables.** Auditing, maintaining aggregates, queueing outbound work. What AFTER cannot do is change the row: assignments to the new row image are discarded because the write is done. Candidates who "fix" a value in an AFTER trigger and see no effect are hitting exactly this. To change the row from AFTER you would have to issue another UPDATE — which re-fires triggers and risks recursion. ## Which to choose The rule that survives contact with reality: **shape the row in BEFORE, react to the row in AFTER.** If the trigger's job is "what should this row be", it is BEFORE. If its job is "now that this happened, do X", it is AFTER. A secondary consideration is failure cost. A BEFORE trigger that rejects saves the engine the write. An AFTER trigger that fails forces a rollback of work already done — slightly more expensive, though correctness is identical because both run in the same transaction. ## Both run inside the transaction Neither timing escapes the invoking transaction. Raising an error in a BEFORE or an AFTER trigger aborts the statement; depending on the engine and settings it aborts the whole transaction. Everything the trigger wrote is rolled back with everything else. This is what makes triggers reliable for invariants and what makes them a bad place for external side effects such as HTTP calls or sending mail — those cannot be rolled back. ## Vendor variation worth knowing - **SQL Server** has no BEFORE trigger at all. Its options are AFTER (the default timing) and INSTEAD OF, which replaces the operation entirely; INSTEAD OF is how people emulate "modify before storing" there. - **PostgreSQL, Oracle, MySQL, DB2** all support BEFORE and AFTER row triggers, with different syntax for reading and writing the row image. - **Oracle** additionally restricts what a row trigger may query on its own table (the mutating-table restriction), which affects BEFORE and AFTER row triggers alike. ## The interview signal Say the one-line rule (modify in BEFORE, react in AFTER), then justify it with the sequence: BEFORE precedes the write so the row is still mutable; AFTER follows it so generated values are visible and mutations are pointless. Mentioning that SQL Server lacks BEFORE, and that both run in the caller's transaction, marks a candidate who has actually used them.
- Why can't you set a column value in an AFTER row trigger?Because the row has already been written to the table and its indexes by the time the AFTER trigger runs, so the trigger's copy of the new row image is informational only and assignments to it are discarded. Changing the stored value from AFTER requires issuing a separate UPDATE, which fires triggers again and can recurse. That is why value shaping belongs in BEFORE.
- If a BEFORE trigger and a constraint would both reject a row, which fires first and does it matter?The BEFORE trigger runs first, before the row is written and before immediate constraints are evaluated, so its error is what the client sees. It matters for error quality — you can return a domain-specific message instead of a constraint-violation code — but not for correctness, since either way nothing is stored. Keep the constraint anyway: it holds even if the trigger is dropped or disabled.
saying these in an interview costs you the question
- Expecting an assignment to the new row inside an AFTER trigger to change the stored value
- Assuming a BEFORE INSERT trigger can read the auto-generated primary key
- Believing AFTER triggers run outside or after the invoking transaction
- Claiming SQL Server supports BEFORE triggers
- Thinking BEFORE triggers run after constraint checking