Lecture 6 — SQL Advanced: Triggers & Functions

Triggers end-to-end (why, MySQL syntax, OLD/NEW, all six timing × event examples) and stored functions including table functions.

Sections

Quick summary

TRIGGER = a statement executed AUTOMATICALLY by the system as a side effect of a
   modification to the database (SQL:1999). Design = (a) the CONDITIONS, (b) the ACTIONS.
USES: enforce business rules · audit logs · validate data · update related tables ·
   logging · stop an operation by raising an error.

MySQL RULES: row-level only (FOR EACH ROW required) · DML triggers only ·
   timing BEFORE|AFTER · events INSERT|UPDATE|DELETE · NO "INSTEAD OF" ·
   at most ONE trigger per timing+event per table.
OLD/NEW:  INSERT → NEW only · DELETE → OLD only · UPDATE → both.
   Only a BEFORE trigger may ASSIGN to NEW.col.
   SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '…' aborts the statement.
   DELIMITER $$ … END$$  DELIMITER ;  (the body contains semicolons).

FUNCTION = a stored program returning EXACTLY ONE value; usable inside SELECT/WHERE/JOIN.
   CREATE FUNCTION f(p T) RETURNS T [DETERMINISTIC] [READS SQL DATA|NO SQL]
   BEGIN DECLARE v T; SELECT c INTO v FROM t WHERE …; RETURN v; END
   Table functions (SQL:2003) return a relation = a parameterised view.
FUNCTION returns one value · PROCEDURE uses CALL · TRIGGER is never called by you.