Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW
Understand trigger timing, granularity (row vs statement), and what NEW and OLD mean inside a trigger function.
What a Trigger Is
A trigger is a function the database calls automatically before or after an INSERT, UPDATE, DELETE, or TRUNCATE on a table.
Two Pieces: Function + Trigger
You write a function that returns trigger, then attach it to a table:
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at := NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();All lessons in this course
- Trigger Anatomy: BEFORE/AFTER, FOR EACH ROW
- PL/pgSQL Function Basics
- DO Blocks and Anonymous Code
- Auditing Tables with Triggers