Views & Triggers

View

A virtual table based on a SELECT query that is saved as an object in the database.

Base table

The underlying real table on which a view is based.

CREATE VIEW syntax

CREATE VIEW view_name AS [SELECT query];

Why are views dynamically updated

The virtual table is recreated on demand each time it is used, so changes to the base table appear immediately in the view.

Three benefits of views

(1) Basis for reports, (2) Security through aliased columns and hidden table names, (3) Filter rows so end-users don't see the full set.

Updatable view

A view that can update attributes in the base tables it is built from.

MySQL updatable view restrictions

Cannot use GROUP BY, HAVING, aggregate functions, DISTINCT, UNION/INTERSECT/MINUS, subqueries in SELECT or FROM, or all types of JOINs; the base table must be key-preserved.

Key-preserved table

A base table whose primary key values remain unique in the view (required for an updatable view).

Trigger

Procedural SQL code automatically invoked by the DBMS when a data manipulation event (INSERT, UPDATE, DELETE) occurs on a table.

When can a trigger fire

BEFORE or AFTER an INSERT, UPDATE, or DELETE on a row.

Trigger syntax (MySQL)

CREATE TRIGGER trigger_name [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON table_name FOR EACH ROW BEGIN SQL_statements; END;

Four common uses of triggers (Oracle's recommendation)

Auditing, automatic generation of derived columns, enforcement of business or security constraints, creation of replica tables for backup.

Trigger vs stored procedure (key difference)

A trigger fires automatically on a data event; a stored procedure must be explicitly called.

Persistent Stored Module (PSM)

A block of standard SQL plus procedural extensions stored and executed at the DBMS server (defined by SQL-99). Used for triggers, stored procedures, and procedural SQL functions.