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...