Views, Assertions and Triggering

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

Views, Assertions and Triggering

Virtual tables, declarative invariants, and code that runs automatically on data change.

🌍 Where this lives: views are how a database serves several audiences from one set of tables. A hospital's schema has one patient table; the billing view exposes the account number and not the diagnosis, the clinical view exposes the diagnosis and not the account, and the research view exposes neither — only anonymised age brackets. Grant access to the view and revoke it on the base table, and the restriction is enforced by the engine rather than by everyone remembering not to select the wrong column. Triggers are the other half: they are how an audit log gets written even when someone updates a row from a SQL console. Search "row level security vs views" for the modern refinement of this idea.

Views

A VIEW is a named query. It stores no data — the query is re-executed each time the view is referenced. CREATE VIEW v_name AS SELECT ...; CREATE OR REPLACE VIEW v_name AS SELECT ...; DROP VIEW v_name [CASCADE]; WHY VIEWS EXIST — four distinct purposes: 1. SIMPLIFICATION — hide a complex join behind a name 2. SECURITY — expose only some columns or rows 3. LOGICAL DATA INDEPENDENCE — absorb schema changes so applications need not change (from the abstraction topic) 4. CONSISTENCY — one definition of "active student" used everywhere, rather than each query re-deriving it VIEW vs MATERIALIZED VIEW VIEW MATERIALIZED VIEW ──────────────────────────────────────────────────────── stores data no YES, on disk freshness always current stale until REFRESH read cost runs the query a simple table scan write cost none REFRESH cost indexable no (indexes on YES base tables) use when the query is the query is cheap expensive and slight staleness is fine REFRESH MATERIALIZED VIEW mv; REFRESH MATERIALIZED VIEW CONCURRENTLY mv; -- no read lock

Updatable views and the disappearing row

A view is AUTOMATICALLY UPDATABLE only if it is simple enough for the DBMS to map each view row back to exactly one base row. Broadly, it must have: · exactly ONE table in FROM · no DISTINCT, GROUP BY, HAVING · no aggregate or window functions · no UNION, INTERSECT, EXCEPT · no LIMIT / OFFSET · every column a simple column reference (not an expression) for INSERT If any of those is violated the view is READ-ONLY, and you need an INSTEAD OF trigger to make it writable. THE DISAPPEARING ROW PROBLEM — verified behaviour: CREATE VIEW v_cs AS SELECT roll, name, marks FROM student WHERE dept = 'ACtE07'; INSERT INTO v_cs (roll, name, marks) VALUES (105, 'Bikash', 75.0); -- ✔ SUCCEEDS SELECT roll, name, dept FROM student WHERE roll = 105; -- roll | name | dept -- -----+--------+------ -- 105 | Bikash | <- dept is NULL! SELECT COUNT(*) FROM v_cs WHERE roll = 105; -- 0 <- the row you just inserted is INVISIBLE The insert did not supply dept, so it defaulted to NULL, and NULL does not satisfy dept = 'ACtE07'. The row exists in the base table but falls outside the view. THE FIX — WITH CHECK OPTION: CREATE VIEW v_cs_checked AS SELECT roll, name, dept, marks FROM student WHERE dept = 'ACtE07' WITH CHECK OPTION; INSERT INTO v_cs_checked (roll,name,dept,marks) VALUES (106,'Test','AExE01',70.0); -- ERROR: new row violates check option for view -- "v_cs_checked" -- DETAIL: Failing row contains (106, Test, AExE01, 70.00). Now any insert or update that would produce a row outside the view is REJECTED. Always add WITH CHECK OPTION to a writable filtered view — otherwise the view silently accepts data it then cannot show you. CASCADED vs LOCAL: WITH CASCADED CHECK OPTION (default) — checks this view's condition AND those of all underlying views WITH LOCAL CHECK OPTION — checks only this view's own condition

views.sql

-- verified against PostgreSQL 18
CREATE TABLE student (roll INT PRIMARY KEY, name TEXT NOT NULL,
                      dept CHAR(6), marks NUMERIC(5,2));
INSERT INTO student VALUES
    (101,'Ram','ACtE07',87.5), (102,'Sita','ACtE07',91.0),
    (103,'Hari','AExE01',55.0), (104,'Gita','AExE01',68.0);

-- ===== 1. a simple, updatable view =====
CREATE VIEW v_cs AS
    SELECT roll, name, marks FROM student WHERE dept = 'ACtE07';

SELECT * FROM v_cs ORDER BY roll;
--  roll | name | marks
--  -----+------+-------
--   101 | Ram  | 87.50
--   102 | Sita | 91.00

-- UPDATE through the view reaches the base table
UPDATE v_cs SET marks = 88.0 WHERE roll = 101;
SELECT roll, marks FROM student WHERE roll = 101;
--  roll | marks
--  -----+-------
--   101 | 88.00     <- base table changed ✔

-- ===== 2. an AGGREGATE view is read-only =====
CREATE VIEW v_summary AS
    SELECT dept, COUNT(*) AS n, AVG(marks) AS avg_m
    FROM   student GROUP BY dept;

SELECT * FROM v_summary ORDER BY dept;
--   dept  | n |        avg_m
--  -------+---+---------------------
--  ACtE07 | 2 | 89.5000000000000000
--  AExE01 | 2 | 61.5000000000000000

UPDATE v_summary SET n = 99 WHERE dept = 'ACtE07';
-- ERROR: cannot update view "v_summary"
-- DETAIL: Views containing GROUP BY are not automatically
--         updatable.
-- HINT:  To enable updating the view, provide an INSTEAD OF
--        UPDATE trigger ...

-- ===== 3. a view for SECURITY =====
CREATE VIEW v_public AS
    SELECT roll, name, dept FROM student;   -- no marks

GRANT SELECT ON v_public TO readonly_role;
REVOKE ALL  ON student   FROM readonly_role;
-- readonly_role CANNOT reach marks at all, even with a
-- hand-written query — it has no privilege on the base
-- table. That is enforcement, not convention.

-- ===== 4. a MATERIALIZED view for an expensive query =====
CREATE MATERIALIZED VIEW mv_dept_stats AS
    SELECT dept, COUNT(*) AS n, AVG(marks) AS avg_m,
           MAX(marks) AS top
    FROM   student GROUP BY dept;

CREATE UNIQUE INDEX ON mv_dept_stats (dept);
    -- ^ possible ONLY because it is materialised;
    --   a plain view cannot be indexed

-- at this point ACtE07 has 2 students, so the materialised
-- view holds n = 2. Now add a third:
INSERT INTO student VALUES (107,'New','ACtE07',99.0);

SELECT n FROM mv_dept_stats WHERE dept = 'ACtE07';
--  n
--  ---
--   2      <- STALE. The base table has 3 rows now, but the
--            stored snapshot still says 2.

REFRESH MATERIALIZED VIEW mv_dept_stats;
SELECT n FROM mv_dept_stats WHERE dept = 'ACtE07';
--  n
--  ---
--   3      <- recomputed from the base table ✔

-- The gap between those two SELECTs is the staleness window.
-- Nothing errors, nothing warns — a query against a
-- materialised view is always a query against a snapshot,
-- and it is your job to know how old that snapshot is.

The staleness of a materialized view is not a bug to work around — it is the whole trade. You are choosing to read a cheap snapshot instead of computing an expensive truth. The design question is only "how stale is acceptable", and the answer belongs to the business, not the DBA. Refresh on a schedule for a dashboard; refresh in a trigger if it must be near-real-time; use a plain view if it must be exact.

Triggers

A TRIGGER is a procedure the DBMS executes automatically in response to a data change. CREATE TRIGGER name { BEFORE | AFTER | INSTEAD OF } { INSERT | UPDATE | DELETE | TRUNCATE } [ OF column_list ] ON table [ FOR EACH { ROW | STATEMENT } ] [ WHEN (condition) ] EXECUTE FUNCTION fn(); TIMING — and it decides what the trigger can do: BEFORE runs before the change. Can MODIFY the row via NEW, or cancel the operation by returning NULL. Use for: validation, defaulting, normalising values. AFTER runs after the change is applied. NEW is read-only for effect. The row is already written. Use for: audit logs, maintaining summary tables, notifying other systems. INSTEAD OF applies to VIEWS only. Replaces the operation entirely — this is what makes a complex view writable. GRANULARITY: FOR EACH ROW fires once per affected row; NEW and OLD are available FOR EACH STATEMENT fires once per statement, even if it touched 0 or 10,000 rows; NEW/OLD are NOT available THE SPECIAL RECORDS: INSERT UPDATE DELETE NEW the new the new not available row row OLD not the old the row being available row deleted RETURN VALUE of a BEFORE ... FOR EACH ROW trigger: return NEW proceed with (possibly modified) NEW return a changed NEW proceed with your modifications return NULL SILENTLY SKIP this row

triggers.sql

-- ===== 1. BEFORE trigger: validate and normalise =====
CREATE OR REPLACE FUNCTION normalise_student()
RETURNS TRIGGER AS $$
BEGIN
    -- clean the data before it is stored
    NEW.name := INITCAP(TRIM(NEW.name));
    IF NEW.marks IS NULL THEN NEW.marks := 0; END IF;
    -- reject what a CHECK cannot express
    IF NEW.marks > 100 THEN
        RAISE EXCEPTION 'marks % exceeds 100', NEW.marks;
    END IF;
    RETURN NEW;   -- must return NEW, or the row is dropped
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_normalise
    BEFORE INSERT OR UPDATE ON student
    FOR EACH ROW EXECUTE FUNCTION normalise_student();

INSERT INTO student (roll,name,dept,marks)
    VALUES (108,'  raM baHAdur  ','ACtE07',NULL);
SELECT roll, name, marks FROM student WHERE roll = 108;
--  roll |    name     | marks
--  -----+-------------+-------
--   108 | Ram Bahadur |  0.00
--  trimmed, title-cased, and NULL defaulted ✔

-- ===== 2. AFTER trigger: an audit log =====
CREATE TABLE student_audit (
    audit_id  SERIAL PRIMARY KEY,
    roll      INTEGER,
    action    CHAR(6),
    old_marks NUMERIC(5,2),
    new_marks NUMERIC(5,2),
    changed_by TEXT DEFAULT CURRENT_USER,
    changed_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE OR REPLACE FUNCTION audit_student()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO student_audit (roll,action,new_marks)
            VALUES (NEW.roll,'INSERT',NEW.marks);
    ELSIF TG_OP = 'UPDATE' THEN
        -- only log an ACTUAL change
        IF OLD.marks IS DISTINCT FROM NEW.marks THEN
            INSERT INTO student_audit
                (roll,action,old_marks,new_marks)
                VALUES (NEW.roll,'UPDATE',OLD.marks,NEW.marks);
        END IF;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO student_audit (roll,action,old_marks)
            VALUES (OLD.roll,'DELETE',OLD.marks);
        RETURN OLD;
    END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit
    AFTER INSERT OR UPDATE OR DELETE ON student
    FOR EACH ROW EXECUTE FUNCTION audit_student();

UPDATE student SET marks = 95 WHERE roll = 101;
UPDATE student SET marks = 95 WHERE roll = 101;  -- no change
DELETE FROM student WHERE roll = 104;

SELECT roll, action, old_marks, new_marks FROM student_audit
ORDER BY audit_id;
--  roll | action | old_marks | new_marks
--  -----+--------+-----------+-----------
--   101 | UPDATE |     88.00 |     95.00
--   104 | DELETE |     68.00 |
--  Only ONE update logged — the second changed nothing, and
--  IS DISTINCT FROM correctly treated it as a no-op.

-- ===== 3. INSTEAD OF trigger: make an aggregate view
-- writable is impossible, but a JOIN view can be =====
CREATE VIEW v_student_dept AS
    SELECT s.roll, s.name, s.dept, d.dname
    FROM   student s LEFT JOIN department d ON d.code = s.dept;

CREATE OR REPLACE FUNCTION ins_student_dept()
RETURNS TRIGGER AS $$
BEGIN
    -- create the department if it does not exist
    INSERT INTO department (code, dname)
        VALUES (NEW.dept, COALESCE(NEW.dname, NEW.dept))
        ON CONFLICT (code) DO NOTHING;
    INSERT INTO student (roll, name, dept)
        VALUES (NEW.roll, NEW.name, NEW.dept);
    RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_ins_v
    INSTEAD OF INSERT ON v_student_dept
    FOR EACH ROW EXECUTE FUNCTION ins_student_dept();
-- now INSERT INTO v_student_dept works, writing to BOTH
-- base tables — something no automatically-updatable view
-- can do.

WHEN TO USE A TRIGGER, and when not to: USE A TRIGGER FOR ✔ audit trails that must capture EVERY change, including ones made outside the application ✔ maintaining a denormalised summary column ✔ rules spanning several tables (no CHECK can do this) ✔ making a complex view writable (INSTEAD OF) ✔ enforcing an invariant the application must not be able to bypass DO NOT USE A TRIGGER FOR ✗ anything a declarative constraint can express — CHECK, UNIQUE and FOREIGN KEY are faster and cannot race ✗ business logic that belongs in the application, where it is testable and visible ✗ calling external systems (sending email, HTTP). The trigger runs inside the transaction; if the external call is slow the transaction holds locks, and if the transaction rolls back the email has already been sent. THE REAL COST OF TRIGGERS — invisibility. A trigger executes with no mention at the call site, so a developer reading UPDATE student SET marks = 95 WHERE roll = 101; has no way to know that three other tables were also modified. That is why heavily-triggered schemas are hard to debug, and why "why did this row change?" becomes a research project. ORDER OF EXECUTION for one row-level operation: 1. BEFORE statement-level triggers 2. BEFORE row-level triggers (may modify NEW) 3. the CHECK constraints and NOT NULL 4. the actual write 5. AFTER row-level triggers 6. AFTER statement-level triggers 7. deferred constraints, at COMMIT Note step 2 before step 3: a BEFORE trigger can fix a value that would otherwise fail a CHECK. That is a legitimate and useful pattern.

Assertions — in the standard, absent in practice

CREATE ASSERTION declares a constraint over the WHOLE database, not a single table: CREATE ASSERTION budget_cap CHECK ( (SELECT SUM(budget) FROM department) <= 50000000 ); STATUS: essentially no production DBMS implements it — not PostgreSQL, MySQL, SQL Server or Oracle. WHY NOT: the engine would have to decide, after every statement anywhere in the database, whether that statement could have invalidated the assertion. In general it cannot know, so the safe implementation is to re-evaluate every assertion after every statement — unbounded cost. WHAT TO USE INSTEAD, in order of preference: 1. a CHECK, if the rule fits in one row 2. a FOREIGN KEY, if the rule is a reference 3. an EXCLUDE constraint, for non-overlap rules (PostgreSQL) 4. TRIGGERS on every table the rule mentions — remembering that you need one per table and correct locking 5. application-level checks, accepting that they can be bypassed THE PRACTICAL POINT for an exam: know that ASSERTION exists in SQL-92, know what it would do, and state that multi-table constraints are implemented with triggers because assertions are unimplemented.

🌍 Go further: the security use of views has been superseded by something more precise: row-level security. Instead of creating one view per role, you attach a policy to the table itself — CREATE POLICY own_rows ON student USING (roll = current_user_roll()) — and every query, from any client, is silently filtered. Multi-tenant SaaS applications are built on this, because it makes "tenant A cannot see tenant B's data" an engine guarantee rather than a WHERE clause everyone must remember. Search "PostgreSQL row level security multi-tenant".

💡 Exam angle: define a view as a stored query, not stored data, and give its four purposes (simplification, security, logical independence, consistency). Know the updatability rules — one table, no GROUP BY, no aggregates, no DISTINCT — and be able to explain WITH CHECK OPTION using the disappearing-row example. For triggers, know BEFORE vs AFTER vs INSTEAD OF, ROW vs STATEMENT granularity, and the NEW/OLD availability table. State that ASSERTION is in the standard but unimplemented, so multi-table rules use triggers.

Syllabus points

Views, assertions, triggers

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.