What you'll learn

  1. Quick answer
  2. Views: a query with a name
  3. Why views are useful
  4. Triggers: code that runs on data change
  5. Why experienced developers are wary of them
  6. What to use instead, usually
  7. FAQ

Quick Answer

A view is a named query you select from like a table; it stores no data and always reflects the current rows. A trigger is code the database runs automatically on insert, update or delete.

Views: a query with a name

CREATE VIEW top_sales AS
SELECT rep, amount FROM sales WHERE amount >= 500;

SELECT * FROM top_sales ORDER BY amount DESC;
-- [('Iqbal', 900), ('Meera', 700), ('Asha', 500)]

You query it exactly like a table. Nothing is stored — the database substitutes the underlying query at execution time.

Which means it is always current:

INSERT INTO sales VALUES ('North', 'New', 600);
SELECT COUNT(*) FROM top_sales;   -- 4, was 3

The new row appeared in the view automatically, because the view is a definition rather than a copy.

Why views are useful

  • Hiding complexity. A five-table join with business rules becomes SELECT * FROM active_enrolments. The complexity is defined once instead of copied into every report.
  • Security. Grant access to a view exposing only non-sensitive columns, without granting access to the underlying table. A support team can see order status without seeing payment details.
  • A stable interface. If the underlying tables are restructured, the view can be redefined so existing queries keep working — which matters when reports live outside your codebase.
  • Consistency. "Active student" defined once in a view is one definition; defined in eleven queries it is eventually eleven slightly different definitions.

The limitation is performance: a view runs its query every time. Selecting from a view over an expensive join costs that join on each call. Some databases offer materialised views, which do store results and must be refreshed — trading freshness for speed.

Triggers: code that runs on data change

CREATE TABLE audit (action TEXT, rep TEXT);

CREATE TRIGGER log_insert AFTER INSERT ON sales
BEGIN
  INSERT INTO audit VALUES ('INSERT', NEW.rep);
END;

INSERT INTO sales VALUES ('South', 'Triggered', 100);
SELECT * FROM audit;
-- [('INSERT', 'Triggered')]

The audit row was written without anything in the application asking for it. That is the appeal: the rule cannot be bypassed, whatever inserts the data — your API, an admin tool, or someone typing SQL directly.

Triggers fire BEFORE or AFTER an INSERT, UPDATE or DELETE. Inside, NEW refers to the incoming row and OLD to the previous one, so an update trigger can see both. BEFORE triggers can modify the row being written; AFTER triggers cannot but are safe for logging.

Why experienced developers are wary of them

Triggers are genuinely useful and genuinely dangerous, and it is worth being able to argue both sides.

Invisible behaviour. The application code says INSERT INTO sales. Three other tables change. Nothing in the codebase mentions them. A developer debugging this can read the application for hours and find no explanation, because the logic lives in the database.

Hard to test. Unit tests against mocked data do not run triggers, so behaviour differs between tests and production.

Cascades. A trigger that writes to a table with its own trigger can chain unexpectedly, and recursive triggers are a genuine failure mode.

Performance. Every insert now does extra work inside the same transaction. A bulk load of a million rows fires the trigger a million times.

The reasonable position: use triggers for data integrity and audit — things that must hold no matter what writes the data. Keep business logic in the application, where it is visible, testable and reviewable.

What to use instead, usually

Before writing a trigger, check whether a simpler mechanism covers it:

  • Constraints — CHECK, NOT NULL, UNIQUE and foreign keys enforce rules declaratively, and the database optimiser understands them.
  • Default values and generated columns handle derived data without procedural code.
  • Application logic — visible in the codebase, reviewed in pull requests, covered by tests.
  • Change data capture — for audit at scale, reading the database's own change log avoids adding write overhead.

In exams, know the syntax, the BEFORE/AFTER distinction and NEW/OLD. In interviews, being able to say "triggers make behaviour invisible to the application, so I would use a constraint here instead" is a stronger answer than reciting the syntax. See also stored procedures, which raise a similar trade-off.

Frequently Asked Questions

Does a view store data? No. A standard view stores only the query definition and runs it each time, so it always reflects current data. Materialised views do store results and need refreshing.
Can I insert into a view? Sometimes. Simple single-table views are often updatable, but views with joins, aggregates or DISTINCT usually are not, because the database cannot determine which underlying rows to change.
What is the difference between BEFORE and AFTER triggers? BEFORE runs prior to the change and can modify the row being written or reject it. AFTER runs once the change is applied and is the right choice for logging and auditing.
Why do developers dislike triggers? Because they make behaviour invisible from the application code. Data changes with no corresponding line in the codebase, which makes debugging and testing considerably harder.
When is a trigger the right choice? For audit trails and data integrity rules that must hold regardless of what writes the data, including manual SQL. Business logic belongs in the application where it is visible and testable.