Views, Materialized Views, and Triggers

Regular views#

A view is a stored, named query — it doesn't hold any data of its own; every time it's queried, Oracle runs the underlying SELECT fresh against the live tables:

CREATE VIEW high_earners AS
SELECT employee_id, first_name, last_name, department_id, salary
FROM employees
WHERE salary > 8000;

SELECT * FROM high_earners WHERE department_id = 20;

Views earn their keep in two recurring situations: hiding a genuinely complex query (multiple joins, analytic functions, business-rule filtering) behind a simple name that other queries and reports can select from as if it were an ordinary table, and restricting what a particular user or application role can see — a view exposing only a handful of non-sensitive columns from employees lets a reporting tool query it without ever being granted access to the underlying table's salary or personal-detail columns directly.

Because a view is just a stored query, it's always current — a plain view over employees reflects a row inserted a millisecond ago exactly as accurately as the raw table would. That currency is also its cost: a view wrapping an expensive aggregate query re-runs that full aggregate every single time it's queried, no matter how often the underlying data actually changes. That's precisely the problem materialized views solve.

Materialized views#

A materialized view looks like a view syntactically, but it actually stores its result set physically on disk, like a real table, and refreshes on whatever schedule you define rather than recomputing on every query:

CREATE MATERIALIZED VIEW mv_department_salary_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT department_id,
       COUNT(*)     AS employee_count,
       SUM(salary)  AS total_salary,
       AVG(salary)  AS avg_salary
FROM employees
GROUP BY department_id;

Querying it reads the precomputed, stored result directly — no aggregation happens at query time at all:

SELECT * FROM mv_department_salary_summary WHERE department_id = 20;

This is exactly the trade a dashboard or reporting screen wants to make: an expensive GROUP BY/aggregate query that would otherwise scan and aggregate the entire employees table on every page load instead reads a small, pre-computed summary table — at the cost of that summary being only as fresh as its last refresh, not perfectly live.

Refresh options#

REFRESH COMPLETE recomputes the entire materialized view from scratch — simple and always correct, but potentially expensive on a large base table. REFRESH FAST instead applies only the changes since the last refresh, which is far cheaper on a large table but requires a materialized view log on the base table to track what changed:

CREATE MATERIALIZED VIEW LOG ON employees
WITH ROWID (department_id, salary)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_department_salary_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT department_id,
       COUNT(*)     AS employee_count,
       SUM(salary)  AS total_salary,
       AVG(salary)  AS avg_salary
FROM employees
GROUP BY department_id;

REFRESH ... ON COMMIT keeps the materialized view current automatically the instant a transaction against employees commits; REFRESH ... ON DEMAND (the default) only refreshes when explicitly told to, either manually or on a schedule:

BEGIN
    DBMS_MVIEW.REFRESH('MV_DEPARTMENT_SALARY_SUMMARY', 'C');  -- 'C' = complete refresh
END;
/

ON COMMIT gives the freshest possible data at the cost of adding refresh work to every relevant transaction; ON DEMAND (often driven by a scheduled job, via DBMS_SCHEDULER) is the usual choice for a nightly or hourly reporting summary where near-real-time freshness isn't actually required.

Triggers#

A trigger is PL/SQL code that runs automatically in response to a DML event (INSERT, UPDATE, DELETE) — or, less commonly, a DDL or database event — on a table, with no explicit call needed from the application.

BEFORE vs AFTER, row-level vs statement-level#

• BEFORE — fires before the triggering statement's effect is applied; the usual choice for validating or modifying values on the way in (like the sequence-populating trigger on the previous page).
• AFTER — fires once the triggering statement's effect has already taken place; the usual choice for side effects that depend on the change having already happened, such as writing an audit record.
• FOR EACH ROW — a row-level trigger, firing once per affected row, with access to :NEW and :OLD pseudorecords holding that row's values after and before the change.
• (no FOR EACH ROW) — a statement-level trigger, firing exactly once per triggering statement regardless of how many rows it affected, with no access to individual row values via :NEW/:OLD.

A real example: an audit-log trigger#

Auditing who changed a salary, and what it changed from and to, is a genuinely common real requirement — exactly the kind of cross-cutting concern a trigger handles without touching a single line of application code:

CREATE TABLE employees_audit (
    audit_id      NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id   NUMBER NOT NULL,
    old_salary    NUMBER(8,2),
    new_salary    NUMBER(8,2),
    changed_by    VARCHAR2(30),
    changed_at    TIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_employees_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_audit (employee_id, old_salary, new_salary, changed_by, changed_at)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, USER, SYSTIMESTAMP);
END;
/

UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 201;

SELECT * FROM employees_audit WHERE employee_id = 201;

AUDIT_ID  EMPLOYEE_ID  OLD_SALARY  NEW_SALARY  CHANGED_BY  CHANGED_AT
1         201          9500.00     9975.00     HR_ADMIN    2026-08-26 10:14:02

AFTER UPDATE OF salary scopes the trigger to fire only when the salary column specifically changes, not on every unrelated update to the row — worth doing whenever a trigger's purpose is tied to one particular column rather than the row in general. This exact pattern — an AFTER UPDATE row-level trigger writing before/after values to an audit table — reappears in the worked example project later in this track, where transferring an employee between departments fires a comparable trigger against department_id.

Common mistakes#

• Reaching for REFRESH FAST without first creating the required materialized view log on the base table — Oracle rejects the fast refresh outright (or silently falls back to a complete refresh, depending on configuration) without one.
• Choosing REFRESH ... ON COMMIT for a materialized view over a table with very high transaction volume, adding real overhead to every single commit against that table for freshness that a scheduled ON DEMAND refresh would have served just as well.
• Writing business logic in a trigger that's better expressed as a stored procedure call the application makes explicitly — a trigger firing invisibly on every UPDATE can make a system's actual behavior much harder to trace than an explicit, visible procedure call.
• Forgetting that a statement-level trigger has no access to :NEW/:OLD — reaching for row values in one is a compile error, and the fix is adding FOR EACH ROW, not working around it inside the trigger body.