Database Reference
In-Depth Information
Immutable fields trigger
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 how about the
cre-
ated_by
and
created_at
values? These can easily be changed in later updates,
but they should never change. Even initially, they can be set to false values, since
default values can be easily overridden by giving any other value in the
INSERT
state-
ment.
So, let's modify our
changestamp()
trigger function into a
usagestamp()
function,
which makes sure that 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
created_*
fields to the needed 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 for creating a
BEFORE INSERT OR UPDATE
trigger:
