SQL Triggers Interview Questions and Answers

Triggers are a topic where interviewers watch for judgment as much as knowledge. They want to know that you can write one, but even more that you know when not to — because triggers introduce invisible behaviour that makes systems hard to reason about. The questions show up in backend, data-engineering and database-administration interviews alike.

The examples use employees(id, name, salary) and an salary_audit(emp_id, old_salary, new_salary, changed_at) table. Trigger syntax is dialect-specific, so examples are labelled; the concepts hold everywhere.

What is a trigger?

A trigger is a block of code the database runs automatically in response to an event — an INSERT, UPDATE or DELETE (and in some engines, DDL or login events) — on a specified table.

The defining trait is automatic invocation: unlike a stored procedure you CALL, a trigger fires by itself whenever its event happens. That automation is both its power (guaranteed enforcement) and its danger (hidden side effects), so frame every trigger answer around that duality.

-- PostgreSQL: trigger + function to audit salary changes
CREATE FUNCTION log_salary() RETURNS trigger AS $$
BEGIN
  INSERT INTO salary_audit(emp_id, old_salary, new_salary, changed_at)
  VALUES (OLD.id, OLD.salary, NEW.salary, now());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees
FOR EACH ROW EXECUTE FUNCTION log_salary();

Q1. BEFORE vs AFTER trigger — when do you use each?

A BEFORE trigger fires before the change is written, so it can validate or alter the incoming values. An AFTER trigger fires once the row is written, so it is the right place to log or to update dependent tables.

Use BEFORE to normalize input (NEW.email = LOWER(NEW.email)) or reject a bad row; use AFTER to write to an audit table or cascade a change, because the row you're referencing definitely exists by then.

-- BEFORE: normalize before storing
CREATE TRIGGER trg_norm BEFORE INSERT ON employees
FOR EACH ROW SET NEW.name = TRIM(NEW.name);   -- MySQL

The rule of thumb to state: "BEFORE to shape or veto the write, AFTER to react to it." That single sentence covers most follow-ups.

Interview note: Trap: "can an AFTER trigger change the row being inserted?" No — the row is already committed to the table state; modifying NEW there has no effect. Shaping the row is a BEFORE job.

Q2. Row-level vs statement-level trigger — what is the difference?

A row-level trigger (FOR EACH ROW) fires once per affected row and can read each row's OLD/NEW values. A statement-level trigger fires once per statement, no matter how many rows it touched.

-- Statement-level: fires once even if the UPDATE hits 10,000 rows
CREATE TRIGGER trg_bulk AFTER UPDATE ON employees
FOR EACH STATEMENT EXECUTE FUNCTION notify_change();  -- PostgreSQL

Choose row-level when you need per-row detail (auditing every change); choose statement-level when one reaction per operation suffices (invalidate a cache once after a bulk update). On a bulk UPDATE, a row-level trigger firing 10,000 times can be a serious performance problem — knowing this is the practical payoff.

Interview note: Follow-up: "MySQL only supports row-level triggers — how do you get statement-level behaviour?" You approximate it in application code or a scheduled job; MySQL has no true statement-level trigger. Dialect knowledge scores here.

Q3. What is an INSTEAD OF trigger?

An INSTEAD OF trigger replaces the operation with your own logic. Its main use is making a complex or multi-table view updatable — the trigger translates an insert/update on the view into writes on the correct base tables.

-- SQL Server: make a join view insertable
CREATE TRIGGER trg_view_ins ON employee_dept_view
INSTEAD OF INSERT AS
BEGIN
  INSERT INTO employees(id, name, dept_id)
  SELECT id, name, dept_id FROM inserted;
END;

Without it, inserting into a multi-table view is impossible because the engine can't decide which base tables to write. The INSTEAD OF trigger supplies that decision. This ties directly to the "when is a view updatable" question.

Interview note: Trap: "does an INSTEAD OF trigger still run the original operation?" No — it replaces it entirely. If you forget to write the base tables inside it, nothing gets inserted.

Q4. What are the main legitimate uses of triggers?

Auditing (logging every change), enforcing rules a CHECK constraint cannot express (cross-table or aggregate conditions), maintaining derived/denormalized columns, and making views updatable via INSTEAD OF.

Auditing is the textbook case because it must happen for every write regardless of which application performed it — exactly what automatic invocation guarantees.

-- Enforce a cross-row rule a CHECK can't: total dept salary cap
CREATE TRIGGER trg_cap BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  IF (SELECT SUM(salary) FROM employees WHERE dept_id = NEW.dept_id) + NEW.salary > 1000000 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Dept salary cap exceeded';
  END IF;
END;   -- MySQL

State each use with its justification; listing use cases without the "why a constraint can't do it" reasoning is a shallower answer.

Interview note: Follow-up: "why not do auditing in the application?" Because a second application, a manual SQL fix, or a batch job would bypass app-level auditing. A trigger catches every path to the table.

Q5. Why are triggers considered risky?

They execute invisibly, so a developer reading the INSERT has no clue extra work happens; they can cascade (one trigger's write fires another trigger); and they add overhead to every write, sometimes turning a fast bulk operation slow.

The debugging problem is the sharpest: when a simple UPDATE is mysteriously slow or produces unexpected rows elsewhere, an unseen trigger is a prime suspect and easy to overlook. Many teams therefore restrict triggers to auditing and treat business logic in triggers as an anti-pattern.

-- Cascade risk: this trigger's UPDATE may fire yet another trigger
UPDATE inventory SET qty = qty - 1 WHERE id = NEW.product_id;

Interview note: Trap: "what is a mutating-table error?" In Oracle, a row-level trigger that queries or modifies the same table it fires on raises ORA-04091. It's a classic trigger pitfall to know by name.

Q6. Can triggers cause infinite loops?

Yes — if trigger A on table X writes to table Y whose trigger writes back to X, they can fire each other endlessly. Databases cap trigger recursion depth to stop runaway loops, and some let you disable recursive firing.

The defence is design discipline: keep trigger side effects one-directional, and be explicit about recursion settings. SQL Server has a RECURSIVE_TRIGGERS database option (off by default) precisely because self-triggering is a known hazard.

Interview note: Follow-up: "how do you break a suspected trigger loop?" Disable the trigger, reproduce the write, and re-enable — isolating which trigger is in the cycle. Then redesign to remove the back-reference.

Q7. How do you access the old and new values inside a trigger?

Through pseudo-records: OLD and NEW (PostgreSQL, MySQL, Oracle) or the inserted and deleted pseudo-tables (SQL Server). INSERT has only NEW/inserted; DELETE has only OLD/deleted; UPDATE has both.

-- Compute the delta on an update
CREATE TRIGGER trg_delta AFTER UPDATE ON accounts
FOR EACH ROW
INSERT INTO ledger(acct_id, delta) VALUES (NEW.id, NEW.balance - OLD.balance);

Knowing which pseudo-record exists for which event is a frequent quick test: asking for OLD in an INSERT trigger is an error because there was no prior row. In SQL Server, remember inserted/deleted are sets, not single rows, so triggers must handle multi-row operations.

Interview note: Trap: "in SQL Server, does a trigger fire per row?" No — it fires per statement with set-based inserted/deleted. Writing SQL Server triggers as if they see one row is a common bug.

Q8. Trigger vs constraint vs application logic — how do you choose?

Prefer a constraint for any declarative rule (NOT NULL, UNIQUE, CHECK, FK) — it is fastest and clearest. Use a trigger only for logic constraints cannot express: cross-table checks, auditing, derived columns. Push evolving business rules to the application where testing and versioning are easier.

-- Constraint, not a trigger, for a simple rule:
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);

The decision order — constraint first, trigger only when necessary, application for business rules — is exactly the judgment interviewers are grading. Reaching for a trigger where a CHECK would do signals inexperience.

Interview note: Follow-up: "auditing via trigger vs CDC?" Change Data Capture / logical replication is the modern, lower-overhead alternative to audit triggers for high-volume tables. Mentioning it shows current awareness.

How to prepare

Build the salary-audit trigger and run both a single UPDATE and a bulk UPDATE, then inspect the audit table and the timing — the difference between row-level and statement-level firing becomes obvious immediately. Then deliberately create a two-table trigger cycle in a scratch database and watch the recursion-limit error; that experience makes the "triggers are risky" answer concrete rather than memorized.

Pair this with the stored procedures questions, since a trigger is essentially an auto-fired procedure, and the constraints set for the "constraint vs trigger" judgment that comes up constantly. The SQL learning path covers the data-integrity model underneath. A mock interview on schema design is where the "when would you reach for a trigger" question naturally surfaces.