Part 120: Database Triggers: BEFORE, AFTER & INSTEAD OF

A Trigger is a stored program that automatically fires whenever a specific DML event (INSERT, UPDATE, DELETE) occurs on a table.

1. Automatic Timestamp Update Trigger

CREATE OR REPLACE FUNCTION update_timestamp_func()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp_func();

Key Takeaways

• Use BEFORE triggers to validate or mutate data before writing.
• Use AFTER triggers for audit logging.

In Part 121, we enter Module 13 to master Transactions, Concurrency & Locking!