Triggers & Active Databases

01 · Motivation

From passive constraints to active rules

A classical database is passive: it stores what you tell it and answers what you ask. An active database also reacts — on its own, inside the DBMS, to events it observes.

The story is one of steadily more expressive integrity control. In 1975 the idea was simply “integrity constraints” as a quality-of-data concern. SQL-92 delivered the declarative half: key constraints, referential integrity, domain constraints, CHECK. You say what must be true and the system enforces it.

CREATE TABLE author (
  idAut  INT PRIMARY KEY,
  name   VARCHAR(30) NOT NULL,
  salary DECIMAL(10,2),
  CHECK (salary >= 10000)
);
CREATE TABLE book (
  idBook    SERIAL PRIMARY KEY,
  title     VARCHAR(200) NOT NULL,
  authorref INT NOT NULL REFERENCES author(idAut) ON DELETE CASCADE
);

That schema already rejects a book whose author does not exist, a duplicate author id, and a salary below 10 000 — and cascades deletions from author to book. All without a line of procedural code.

But declarative constraints can only express conditions on a state. They cannot say “when this changes, also do that”, they cannot compare the value before a change with the value after, and they cannot maintain a derived quantity. SQL:1999 answers with triggers: a procedural specification of reactive behaviour, compiled and stored in the DBMS like a stored procedure but invoked by events rather than by a client.

02 · Syntax

ECA and the SQL:1999 trigger

Every trigger, in every dialect, has the same three parts: whenever event E occurs, if condition C holds, execute action A.

• Event — normally a modification of the database state: insert, delete, update.
• Condition — a predicate identifying the situations in which the action is actually required.
• Action — a generic update statement or stored procedure; usually database updates, but it may also raise an error.

The SQL:1999 syntax maps one clause to each:

create trigger <TriggerName>
  { before | after }                                    -- when, relative to the change
  { insert | delete | update [of <Column>] } on <Table>  -- E: the event
  referencing { [ old table [as] <OldTableAlias> ]
                [ new table [as] <NewTableAlias> ]
              | [ old [row] [as] <OldTupleName> ]
                [ new [row] [as] <NewTupleName> ] }
  [ for each { row | statement } ]                       -- granularity
  [ when <Condition> ]                                   -- C: the condition
  <SQLProceduralStatement>                               -- A: the action

Two clauses do most of the work and are where marks are lost: for each chooses the granularity, and referencing names the transition variables the condition and action can see. The next two sections take them in turn.

03 · Mechanics

Transition variables and transition tables

A trigger’s power comes from seeing both states — before and after the modification. The syntax for that depends on the granularity.

• Row-level (for each row): the tuple variables old and new hold the values of the row under consideration, before and after the change.
• Statement-level (for each statement): the table variables old table and new table hold all affected rows, before and after.

Which of them exist depends on the event, and this is a common slip:

An insert has no before-state and a delete has no after-state — referring to the missing one is a syntax error, not an empty set.

The canonical illustration is replicating a table. Keep T2 a copy of T1:

CREATE TRIGGER REPLIC_INS
AFTER INSERT ON T1
FOR EACH ROW
  INSERT INTO T2 VALUES (new.ID, new.VALUE);

CREATE TRIGGER REPLIC_DEL
AFTER DELETE ON T1
FOR EACH ROW
  DELETE FROM T2 WHERE T2.ID = old.ID;

CREATE TRIGGER REPLIC_UPD
AFTER UPDATE OF VALUE ON T1
FOR EACH ROW
WHEN new.ID = old.ID
  UPDATE T2 SET T2.VALUE = new.VALUE WHERE T2.ID = new.ID;

Conditional replication makes the point sharper. Replicate only rows with VALUE >= 10, and a single UPDATE event now splits into three rules, because a row can enter the replica, stay in it, or leave it:

-- newly relevant: insert into the replica
WHEN (old.VALUE < 10 AND new.VALUE >= 10)   → INSERT INTO T2 …
-- still relevant: propagate the change
WHEN (old.VALUE >= 10 AND new.VALUE >= 10 AND old.VALUE != new.VALUE) → UPDATE T2 …
-- no longer relevant: remove from the replica
WHEN (old.VALUE >= 10 AND new.VALUE < 10)   → DELETE FROM T2 …

That enter/stay/leave split is the shape of most trigger exercises. Recognising it early saves time.

04 · Mechanics

BEFORE vs AFTER, row vs statement

Two independent binary choices give four combinations. Picking the wrong cell is the single most common way to lose marks on a trigger question.

The classic drill is the FLIGHT exercise: given Flight(passengerName, airline, flightNumber, date, flightMiles), reset the miles to 300 whenever an insert or update would leave them below 300. Written four ways:

-- BEFORE, row level: modify the modification in flight
create trigger 300Miles before insert on Flight
for each row when new.flightMiles < 300
begin set new.flightMiles = 300; end;

-- AFTER, row level: re-update the row you just wrote
create trigger 300Miles after insert on Flight
for each row when new.flightMiles < 300
begin
  update Flight set flightMiles = 300
  where passengerName = new.passengerName
    and flightNumber = new.flightNumber and date = new.date;
end;                                    -- NOT POSSIBLE IN SOME DBMSs

-- AFTER, statement level: one set-oriented repair
create trigger 300Miles after insert on Flight
for each statement referencing new table as NEW_T
begin
  update Flight set flightMiles = 300
  where (passengerName, flightNumber, date) IN
        (select passengerName, flightNumber, date from NEW_T where flightMiles < 300);
end;

The BEFORE version is not merely stylistically nicer — it is one statement where the AFTER version is two, and several DBMSs (MySQL among them) forbid a trigger from updating the very table whose modification fired it. When the goal is to “modify a modification”, BEFORE is both correct and cheaper.

05 · Execution

Execution order, cascading, and termination

Several triggers may respond to one event, and a trigger’s action may fire further triggers. Two questions follow: in what order, and does it stop?

SQL:1999 prescribes the ordering between categories:

1. BEFORE statement-level triggers
2. BEFORE row-level triggers
3. the modification is applied and integrity constraints are checked
4. AFTER row-level triggers
5. AFTER statement-level triggers

Within one category the order is implementation-defined — typically by definition time (older first) or alphabetically by name. Do not rely on it.

Cascading is when the action of T1 fires T2. Recursive cascading is when a statement on a table eventually re-generates the same event on the same table — looping.

The tool for reasoning about termination is the triggering graph: one node per trigger, and an arc from i to j when the action of Ti may activate Tj. It is built by simple syntactic analysis, and the theorem is one-directional:

How bad can it get? The lectures trace a two-trigger example on Postgres that never stops, and it repays walking through slowly, because the surprise is not the loop — it is what the delayed triggers see.

Real systems put a lid on it in different ways, and knowing which is which is worth a line in an answer: MySQL forbids a trigger from touching a table already in use by the invoking statement; PostgreSQL places no limit on cascade depth and makes it the programmer’s responsibility; SQL Server and Oracle both cap nesting at 32 levels.

06 · Worked example

Incrementally maintaining derived data

This is the exercise the exam actually sets. A quantity is defined by a query; you must keep a table holding that quantity correct, updating it by deltas rather than recomputing it.

A view is a virtual table defined by a stored query. When queries against a view are far more frequent than updates to its base tables, you can materialize it — store the result — and some systems offer CREATE MATERIALIZED VIEW to do so automatically. Where they do not, triggers do the job.

Take the personnel cost per department:

CREATE VIEW deptcost AS
SELECT d.DeptNum AS dept, coalesce(sum(e.salary), 0) AS totCost
FROM dept d LEFT JOIN emp e ON e.dept = d.DeptNum
GROUP BY d.DeptNum;

Recomputing that sum on every change would be absurd, because most changes touch one row of the result:

• updating an employee’s id — no effect at all;
• inserting, deleting, or changing the salary of an employee — one row of deptcost;
• moving an employee between departments — exactly two rows;
• inserting or deleting a department — one row.

So the maintenance is a set of small delta updates. The two representative triggers:

create trigger Incremental_InsEmp
after insert on emp for each row
  update deptcost set totCost = totCost + new.salary where dept = new.dept;

create trigger Incremental_SalaryUpdate
after update of salary on emp for each row
when old.dept = new.dept
  update deptcost set totCost = totCost - old.salary + new.salary where dept = new.dept;

totCost = totCost - old.salary + new.salary is the delta-update idiom, and it recurs in every exam of this family. Note also the when old.dept = new.dept guard: a salary change and a department change in one statement is a different case, handled by its own trigger that decrements the old department and increments the new one.

07 · Worked example

Hierarchies and recursive cascading

The second worked family is a tree whose aggregate must propagate upward — the case where recursive cascading is not a bug but the mechanism.

Take Product(ID, Name, SuperProduct, OwnWeight, TotalWeight), where each product has an own weight and a parent, and TotalWeight is the product’s own weight plus the total weights of its children. Root products have SuperProduct = NULL. Users may insert products, delete products, and change a product’s parent.

Four triggers, and the third is the interesting one:

• T1 — after insert of a product: set its total weight to its own weight.
• T2 — after delete, when the parent is not null: decrease the parent’s total weight.
• T3 — after update of totalweight, when the parent is not null and the value actually changed: apply the same delta to the parent. This is the propagation rule, and it fires itself all the way to the root.
• T4 — after update of superproduct: decrease the old parent, increase the new one.

CREATE TRIGGER product_AFTER_UPDATE_TOTALWEIGHT
AFTER UPDATE of totalweight ON product
FOR EACH ROW
WHEN new.superproduct is not null AND new.totalweight != old.totalweight
  -- remember the three-valued logic
BEGIN
  UPDATE product SET totalweight = totalweight + (new.totalweight - old.totalweight)
  WHERE ID = new.superproduct;
END

T3’s own action is an update of totalweight, so it re-activates itself on the parent, then the grandparent, and terminates at the root where superproduct IS NULL. The triggering graph is cyclic; termination comes from the data — the tree is finite and each step moves strictly upward.

Deletion of a deleted product’s children needs no trigger at all — ON DELETE CASCADE on the self-referencing foreign key handles it. Which brings us to the design rule.

08 · Design

Triggers versus declarative constraints

The first design principle in the lectures is also the one graders check: do not write a trigger for something the DBMS already does declaratively.

The full list of principles is short and worth knowing:

• Use triggers to guarantee that a specific operation is accompanied by its related actions.
• Do not duplicate features already built into the DBMS — do not write a trigger to reject bad data if a declarative integrity constraint expresses the same rule.
• Keep triggers small; past roughly 60 lines, move the body into a stored procedure.
• Use them only for centralized, global operations that must fire regardless of which user or application issued the statement.
• Avoid recursion unless it is genuinely the mechanism.
• Always document them — their behaviour is hidden from anyone reading the application code.

The July 2025 paper turns this into an explicit instruction: it asks for a trigger system enforcing an ISA hierarchy’s coherence, and adds “you can manage some of the constraints in the table definition, but if you do so please include the table creation statements”. The official solution splits the work exactly along the principle — a CHECK constraint for the selector/attribute consistency, ON DELETE SET NULL / NO ACTION / CASCADE for every deletion rule, and triggers only for the two conditions SQL cannot state declaratively:

CREATE TRIGGER supervisorFK
BEFORE INSERT ON EMP REFERENCING NEW AS N
FOR EACH ROW
WHEN (EXISTS (SELECT * FROM EMP WHERE N.supervisor = EMP.ID AND EMP.isProf IS FALSE))
  SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'The supervisor is not a Prof!';

Note the pattern for rejecting a modification: a BEFORE trigger whose condition detects the violation and whose action raises SIGNAL. An AFTER trigger would have to compensate with a delete.