CSE Roadmap

Common tier · about 3 hours · 5 parts

Part 3 of 5 · 1 min read

Triggers

Logic that runs automatically in response to a data change.

Think of a smoke detector that sounds an alarm automatically the moment smoke is present, with nobody needing to remember to check for it.

In plain words: a trigger runs automatically before or after an INSERT, UPDATE or DELETE on a specific table. It cannot be called directly. It fires whenever the triggering event happens, whether or not the application that caused it even knows the trigger exists.

A worked example. Automatically recording a last_modified timestamp whenever a book's row is updated.

The key lines.

CREATE FUNCTION set_last_modified() RETURNS TRIGGER AS $$
BEGIN
    NEW.last_modified := NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER books_set_last_modified
BEFORE UPDATE ON books
FOR EACH ROW EXECUTE FUNCTION set_last_modified();

Trigger

A trigger is logic that runs automatically in response to an INSERT, UPDATE or DELETE on a table, rather than being called directly by name.

Example: books_set_last_modified runs on every update to books, whether the application that performed the update knew the trigger existed or not.