Modifying the NEW record

Another form of auditing that is frequently used is to log information in fields in the same row as the data. As an example, let's define a trigger that logs the time and the active user in the last_changed_at and last_changed_by fields at each INSERT and UPDATE trigger. In the row-level BEFORE triggers, you can modify what actually gets written by changing the NEW record. You can either assign values to some fields or even return a different record with the same structure. For example, if you return OLD from the UPDATE trigger, you effectively make sure that the row can't be updated.

The timestamping trigger

To form the basis of our audit logging in the table, we start by creating a trigger that sets the user who made the last change and when the change occurred:

CREATE OR REPLACE FUNCTION changestamp() 
  RETURNS TRIGGER AS $$ 
BEGIN 
    NEW.last_changed_by = SESSION_USER; 
    NEW.last_changed_at = CURRENT_TIMESTAMP; 
    RETURN NEW; 
END; 
$$ LANGUAGE plpgsql;...