Triggers
A trigger runs a function automatically when a row is inserted, updated or deleted.
In this page:
Syntax
CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
EXECUTE FUNCTION function_name();Triggers
A trigger is attached to a table with CREATE TRIGGER ... BEFORE or AFTER INSERT/UPDATE/DELETE and calls a trigger function that returns the special type trigger.
Inside it, NEW and OLD hold the row values. Common uses are audit logs, keeping updated_at current and enforcing complex rules. Triggers add hidden behaviour, so document them.
Note:
BEFORE triggers can modify NEW; AFTER triggers are for side effects.
Example: Triggers
shop=# CREATE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
shop$# BEGIN
shop$# NEW.updated_at := now();
shop$# RETURN NEW;
shop$# END $$;
shop=# CREATE TRIGGER trg_users_touch BEFORE UPDATE ON users
shop-# FOR EACH ROW EXECUTE FUNCTION touch_updated_at();
CREATE TRIGGER
shop=# UPDATE users SET name = 'Ada L.' WHERE id = 1;
UPDATE 1⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Hidden logic surprising other developers
- Infinite trigger loops
- Heavy work inside triggers
Chapter Summary
- Triggers fire on INSERT, UPDATE, DELETE
- NEW and OLD hold row values
- BEFORE can change the row
- Use sparingly and document them
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: