CREATE TRIGGER attaches a function to a table, set to run BEFORE or AFTER a specific event (INSERT, UPDATE, DELETE). A BEFORE trigger can inspect and even modify the row before it's written; an AFTER trigger runs once the change has already happened, commonly used for logging or cascading side effects. Because triggers fire automatically regardless of which application or query caused the change, they're a genuine way to enforce a rule everywhere — and also a common source of confusing, hard-to-trace behavior if overused.
A BEFORE trigger: auto-updating a timestamp
NEW refers to the row as it's about to be written; a BEFORE trigger can modify NEW's fields before the actual write happens — here, forcing updated_at to always reflect the true update time, no matter what the calling application sent.
CREATE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_set_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();An AFTER trigger: audit logging
OLD refers to the row's state before the change — useful in AFTER triggers to record what was actually deleted or changed, since the original row is no longer directly queryable at that point.
CREATE FUNCTION log_deletion() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO deletion_log (table_name, deleted_id, deleted_at)
VALUES ('products', OLD.id, CURRENT_TIMESTAMP);
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_log_deletion
AFTER DELETE ON products
FOR EACH ROW
EXECUTE FUNCTION log_deletion();The real risk: hidden, hard-to-trace logic
A trigger fires with no line of application code calling it — which means debugging "why did this row change unexpectedly?" can require knowing a trigger exists at all before you'd even think to look for it. Many teams deliberately limit triggers to narrow, well-documented uses (audit logs, timestamp maintenance) rather than embedding significant business logic in them, precisely because the logic becomes invisible from the application's point of view.