Support wants a record of every email change. Each program that changes an email could write that record, but one of them will forget. A triggertriggerA rule that makes the database run a function by itself when rows of a table change makes the database write it on every change.

Two parts

1. The trigger function. It returns the type trigger. Inside it, OLD is the row before the change and NEW is the row after it:

CREATE TABLE email_log (customer_id integer, old_email text, new_email text);

CREATE FUNCTION log_email_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO email_log VALUES (OLD.id, OLD.email, NEW.email);
  RETURN NEW;
END;
$$;

2. The trigger. It connects the function to a table and an event:

CREATE TRIGGER log_email
AFTER UPDATE OF email ON customers
FOR EACH ROW
EXECUTE FUNCTION log_email_change();

• AFTER UPDATE fires after each update. OF email limits it to updates that set email. Without OF …, every UPDATE fires it.
• INSERT and DELETE work the same. An INSERT has only NEW; a DELETE has only OLD.
• BEFORE triggers run first and can change NEW before PostgreSQL stores it.
• RETURN NEW; ends the function. An AFTER trigger ignores the value, but the function must return one.

Now change an email and read the log:

UPDATE customers SET email = 'ava@monster.example' WHERE id = 1;
SELECT * FROM email_log;