skip to content

What does an INSTEAD OF trigger do differently from BEFORE and AFTER triggers, and on what kind of database object is it normally defined?

level: middleimportance: should knowfreq 36%

answer

  1. replaces the DML, does not wrap it
  2. empty body = silent successful no-op
  3. views: no storage, needs a human mapping
  4. one INSTEAD OF per object per operation
  5. row-level only; base-table triggers still fire

basics

~20 s

BEFORE and AFTER triggers run around an operation the engine still performs. An INSTEAD OF trigger replaces it: the original INSERT, UPDATE or DELETE never executes, and whatever the trigger body writes is the entire effect. It is normally defined on a view.

solid answer

~60 s

BEFORE and AFTER are *hooks*: the engine performs the DML either way, and the trigger runs around it. INSTEAD OF is a *substitution*. When the statement targets the object, the engine executes the trigger body and nothing else — if the body writes nothing, the statement reports success and no data changed. Its natural home is a view. A view has no storage, so a plain INSERT against a non-trivially-derived view has no defined meaning; an INSTEAD OF trigger supplies that meaning by translating the statement into DML on the underlying base tables, including fan-out across several tables. Mechanics worth knowing: it is row-level in the common engines, so it receives each row the statement would have affected; engines generally allow only **one** INSTEAD OF trigger per object per operation, since two substitutions cannot both be "instead of"; and the DML it issues against base tables fires those tables' own BEFORE and AFTER triggers normally. It also runs in the invoking transaction, so it is atomic with the caller's work.

code

sql · 11 lines
sql
CREATE VIEW customer_profile AS
SELECT c.id, c.name, p.phone
FROM customer c JOIN profile p ON p.customer_id = c.id;

CREATE TRIGGER trg_customer_profile_ins
  INSTEAD OF INSERT ON customer_profile
  FOR EACH ROW EXECUTE FUNCTION insert_customer_profile();

-- inside insert_customer_profile():
--   INSERT INTO customer (id, name) VALUES (NEW.id, NEW.name);
--   INSERT INTO profile (customer_id, phone) VALUES (NEW.id, NEW.phone);

go deeper

for a junior

Say it replaces the operation instead of running around it, and that it is used to make views writable.

for a middle

Add the mechanics: row-level, one per object per operation, and the body's DML fires base-table triggers normally.

for a senior

Discuss the failure modes — silent no-ops, translations that break on multi-row DML, ignored columns — and how you would test them.

for a principal

Weigh a view plus INSTEAD OF as a stable write API against schema evolution and the invisibility of the mapping to clients.

## Hook versus substitution The three timings answer different questions. - **BEFORE**: run this just before the engine applies the change — the change still happens, and the trigger may adjust the row. - **AFTER**: run this once the engine has applied the change — again, the change happens. - **INSTEAD OF**: do not apply the change at all; run this instead. That last one is a genuine control-flow difference, not a scheduling difference. The statement the client issued is *discarded* as an operation. Whatever the trigger body does — possibly nothing, possibly writes to five other tables — is the complete effect. This is why an INSTEAD OF trigger with an empty body turns every INSERT against that object into a silent no-op that still returns success. Debugging "the insert succeeded but the data is not there" almost always ends at an INSTEAD OF trigger. ## Why views are the natural target A table has storage, so `INSERT INTO t` has an obvious meaning. A view is a stored query; it has no storage of its own. For simple derivations an engine can mechanically translate DML on the view into DML on the single underlying table. But once the view joins several tables, aggregates, or computes columns, there is no mechanical translation — the engine cannot know which base rows a given view row should create, or how to distribute a value across joined tables. INSTEAD OF triggers exist to let a human supply that translation. The trigger body receives the row the client wanted and writes whatever combination of base-table rows expresses it: insert a parent and a child, update one table and ignore the columns owned by another, delete from the child but only mark the parent inactive. The view then behaves like a writable object to the client, with the mapping written explicitly rather than inferred. ## Mechanics that come up **Row-level.** In the common engines an INSTEAD OF trigger is defined FOR EACH ROW (statement-level INSTEAD OF is generally not allowed), because the substitution is inherently per-row: for each row the statement would have produced, do this instead. The trigger sees the incoming row image for INSERT, the old and new images for UPDATE, and the old image for DELETE. **One per operation.** Engines allow at most one INSTEAD OF trigger per object per operation. Two substitutions would be contradictory. This differs from BEFORE and AFTER, where several triggers can stack. **Nested trigger firing.** The DML the body issues against base tables is ordinary DML: those tables' BEFORE and AFTER triggers fire, constraints apply, and cascades cascade. The substitution applies only to the object the client named. **Transaction scope.** Like other triggers, it runs inside the caller's transaction, so the translated writes commit or roll back with the caller's work. **Return values and generated keys.** Because the client's statement did not really touch the named object, features that report what was written — returning generated identifiers, affected-row counts — depend on what the trigger reports back, and engines differ in how much they let you control that. Do not assume the client's driver sees a generated key from a base table automatically. ## Where else it can appear The target object is normally a view. SQL Server additionally permits INSTEAD OF triggers on tables, where the classic use is to intercept and rewrite an operation — for example turning a DELETE into a soft-delete UPDATE. Oracle restricts them to views (plus a form for nested-table columns). PostgreSQL allows them on views only. So the safe, portable statement is: **INSTEAD OF triggers are for views**, with SQL Server as the notable extension. ## Trade-offs The upside is a clean, stable write API: clients target the view, and the physical layout behind it can change without touching client code. The downside is the usual trigger cost, amplified — the translation logic is invisible from the client's perspective, so the mapping between what a client asked for and what was stored exists only inside the trigger body. It is also easy to write a translation that is correct for single-row DML and wrong for multi-row statements, or that silently ignores columns the client set. Both classes of bug pass review because the code reads plausibly. ## The interview signal Say "it replaces the operation rather than wrapping it", name views as the target, and mention the two mechanics that show real use: only one per operation, and the DML it issues fires the base tables' own triggers.

  • A client runs an INSERT against a view, gets no error, and finds no new data anywhere. What is the most likely cause?
    An INSTEAD OF INSERT trigger on the view whose body did not write the row — either it is empty, or a condition inside it skipped the write. Because the trigger replaces the operation entirely, the statement completes successfully while nothing is stored. Reading the trigger body is the fastest path to the answer.
  • Do the base tables' own triggers run when an INSTEAD OF trigger writes to them?
    Yes. The statements inside the trigger body are ordinary DML against those tables, so their BEFORE and AFTER triggers fire, their constraints are enforced, and any cascading behavior applies. The substitution only replaces the operation on the object the client named, not on anything the body touches afterwards.

saying these in an interview costs you the question

  • Thinking an INSTEAD OF trigger runs before the operation and then the operation also runs
  • Assuming several INSTEAD OF triggers can be stacked on the same object and operation
  • Expecting base-table triggers to be suppressed when the trigger body writes to them
  • Believing an INSTEAD OF trigger escapes the caller's transaction
  • Assuming it can be defined FOR EACH STATEMENT

context

resolving links…