A view aggregates and joins several tables, so the engine will not accept writes against it. How does an INSTEAD OF trigger let you support writes anyway, and what are the risks of that approach?

## What the trigger replaces For an auto-updatable view, the engine rewrites the incoming statement into an equivalent statement on the base table. When the view has joins, aggregates, unions or expressions, that rewrite does not exist. An INSTEAD OF trigger fills the gap: it is attached to the view, and when DML arrives, the engine runs the trigger body *instead of* attempting any write of its own. If the trigger body does nothing, nothing happens and the statement still reports success — the write is entirely whatever the trigger chooses to do. The trigger sees the proposed rows. For an INSERT it gets the new row image as the caller supplied it (columns the view does not expose are simply absent); for an UPDATE it gets old and new images; for a DELETE, the old image. From those it decides which base tables to modify. ## What it makes possible - **Multi-table writes.** A view joining customer and address can accept one INSERT and split it into two inserts in the right order, propagating the generated customer id into the address row. - **Column mapping.** A view exposing full_name can split it into first and last name columns on write. - **Soft deletes.** A DELETE through a view becomes an UPDATE setting deleted_at, so history is preserved while callers use ordinary SQL. - **Legacy facades.** A view can present the shape an old application expects while the trigger writes into a restructured schema — a genuinely useful migration tool. - **Validation and defaulting.** Values the view hides can be supplied centrally. ## The risks, in order of how often they bite **Row-at-a-time performance.** Most implementations fire INSTEAD OF triggers per affected row. A bulk statement therefore runs the trigger body once per row, each executing its own statements. Bulk loads through such a view can be orders of magnitude slower than writing to base tables directly. Test with realistic batch sizes, not with single rows. **Multi-row and set semantics.** Trigger code written with one row in mind often breaks on multi-row statements — in engines where the trigger fires per statement with transition tables, forgetting to iterate is a silent data bug rather than an error. Also, the affected-row count the client receives may no longer reflect what actually changed, which quietly breaks ORMs and optimistic-locking checks that assert "exactly one row updated". **Bypassed constraint semantics.** The view's own WITH CHECK OPTION generally does not apply to what a trigger does, so a trigger can write rows the view cannot see. Base-table constraints still fire, but their errors surface from inside the trigger, often with a message that means nothing to the caller. **Ordering and partial failure.** Writing parent before child, resolving generated keys, and deciding what happens when the second statement fails are all your design decisions. The statement runs inside the caller's transaction, so a raised error rolls the whole thing back — usually the behaviour you want, but only if you actually raise instead of swallowing. **Invisibility.** The most important risk is organisational. An application executes what looks like a single-table INSERT; three tables change, an audit row appears, and a delete does not delete. Nothing at the call site says so. Debugging, code review and incident response all get harder, and the logic lives in a deployment unit that often has weaker review and test discipline than application code. **Interaction with other triggers.** INSTEAD OF logic on the view plus BEFORE/AFTER triggers on the base tables produces an execution order few people can recall correctly, and recursion is easy to introduce. ## When it is the right call Reach for it when the write surface must stay stable while the schema beneath moves — legacy client compatibility, a staged migration, or a genuinely useful multi-table facade owned by the data team. Reach for it when the alternative is duplicating the same mapping logic across several applications that you do not control. Avoid it when the application could simply write to the base tables. "The ORM only knows how to write to one table" is a weak reason to move business logic into the database permanently. Also avoid it on hot bulk paths, where the per-row cost dominates. If you do adopt it: keep the body small and deterministic, iterate correctly over multi-row input, validate the resulting row against the view's intent explicitly since the check option will not, raise clear errors rather than silently skipping rows, version the trigger with migrations, and cover it with tests that exercise multi-row statements and failure paths. ## Interview framing Explain the mechanism first — the engine runs your code in place of its rewrite — then give one concrete use (splitting an insert across two tables, or a soft delete), then the risks: per-row performance, multi-row correctness, affected-row counts, no check-option enforcement, and the invisibility of server-side behaviour to application authors. Finishing with "and often the right answer is to write to the base tables" reads as production judgement rather than enthusiasm for a feature.