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.