When you are depending on the fields in the rows as part of your audit record, you need to ensure that the values reflect reality. We were able to make sure that the last_changed_* fields always contain the correct value, but what about the created_by and created_at values? These can be easily changed in later updates, but they should never change. Even initially, they can be set to false values, since the default values can be easily overridden by giving any other value in the INSERT statement.

So, let's modify our changestamp() trigger function into a usagestamp() function, which makes sure that the initial values are what they should be and that they stay like that:

CREATE OR REPLACE FUNCTION usagestamp()
  RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.created_by = SESSION_USER;
        NEW.created_at = CURRENT_TIMESTAMP;
    ELSE
        NEW.created_by = OLD.created_by;
        NEW.created_at = OLD.created_at;    
    END IF;

    NEW.last_changed_by = SESSION_USER;
    NEW.last_changed_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

In case of INSERT, we set the created_* fields to the required values, regardless of what the INSERT query tries to set them to. In case of UPDATE, we just carry over the old values, again overriding any attempted changes.

This function then needs to be used in order to create a BEFORE INSERT OR UPDATE trigger:

CREATE TRIGGER usagestamp
  BEFORE INSERT OR UPDATE ON modify_test
  FOR EACH ROW  
EXECUTE PROCEDURE usagestamp();

Now, let's try to update the created audit log information. First, we will need to drop the original trigger so that we don't have two triggers firing on the same table. Then, we will try to change the values of created_by and created_at:

postgres=# DROP TRIGGER changestamp ON modify_test;
DROP TRIGGER
postgres=# UPDATE modify_test SET created_by = 'notpostgres', created_at = '2000-01-01';
UPDATE 1
postgres=# select * from modify_test;
-[ RECORD 1 ]---+---------------------------
id              | 1
data            | something else
created_by      | postgres
created_at      | 2013-04-15 09:28:23.966179
last_changed_by | postgres
last_changed_at | 2013-04-15 09:33:25.386006

From the results, you can see that the created information is still the same, but the last changed information has been updated.