Triggers, Stored Procedures & SQL/PSM

Two ways to put logic in the database

The database can hold *behavior*, not just data. Two mechanisms, and the contrast is the exam's whole question:

- A trigger is a named set of SQL statements that fires automatically when a data-modification event (INSERT, UPDATE, DELETE) — or a specified DDL event — occurs on a table. You never *call* it; the event activates it. A trigger has three parts: the event (what activates it), the condition (an optional WHEN/FOR EACH ROW qualifier), and the action (the SQL it runs). Timing can be BEFORE (validate/prevent the change), AFTER (audit/log/cascade — the most common), or INSTEAD OF (replace the action, e.g. to make a view updatable). - A stored procedure (a *routine*) is a named block of procedural + SQL statements that must be explicitly called, and can take input, output, and input/output parameters. A function is the related routine that returns exactly one value and has only input parameters.

The one-line contrast: trigger = implicit, event-driven; procedure = explicit, called. Both are stored in the database and apply to every application that touches it.

SQL/PSM: making SQL computationally complete

Plain SQL is set-oriented and not computationally complete — it has no general loops or branches. Persistent Stored Modules (SQL/PSM), added in SQL:1999, supply the missing flow control: IF-THEN-ELSE, WHILE, LOOP, FOR, and CASE. With SQL/PSM the procedural logic for a business rule can live *in the database* rather than only in a host language. (Oracle's PL/SQL and SQL Server's Transact-SQL are vendor predecessors of the same idea; in Postgres it is PL/pgSQL.)

The impedance mismatch and embedding SQL in a 3GL

SQL is set-oriented (it returns whole result tables); a 3GL like Java or Python is record-oriented (it processes one row at a time). That gap is the impedance mismatch, bridged with a *cursor* that steps through a result set row by row. You embed SQL in a 3GL for three reasons the exam asks for (6-19): SQL alone is not computationally complete (no rich flow control); a 3GL gives finer control over transactions, timing, and error handling; and a 3GL builds the user interfaces and integrates database logic with application logic.

Worked example: an AFTER trigger for an audit log

The classic use is an audit trail — after a registration is deleted, record it. Reading the parts: the event is AFTER DELETE ON registrations, the condition is FOR EACH ROW, the action inserts the old row's keys into a log table.

CREATE TABLE drop_log (
  student_id int NOT NULL,
  section_id int NOT NULL,
  dropped_at timestamp DEFAULT now()
);

CREATE FUNCTION log_drop() RETURNS trigger AS $$
BEGIN
  INSERT INTO drop_log (student_id, section_id)
  VALUES (OLD.student_id, OLD.section_id);
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER reg_drop_audit
AFTER DELETE ON registrations
FOR EACH ROW
EXECUTE FUNCTION log_drop();

(Postgres splits the trigger's *action* into a FUNCTION that the CREATE TRIGGER then binds — the textbook writes the action inline, but the three parts are the same: event = AFTER DELETE, condition = FOR EACH ROW, action = the insert. The trigger fires by itself whenever a registration row is deleted — nobody calls it.)