Database Reference
In-Depth Information
Modifying the NEW record
Another form of auditing frequently used is to log information in fields in the same
row as the data. As an example, let's define a trigger which logs the time and active
user in fields
last_changed_at
and
last_changed_by
fields at each
INSERT
and
UPDATE
. In 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 re-
turn 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.
Timestamping trigger
To form the basis of our audit logging in the table, we start with 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;
Of course, this works only on a table which has correct fields:
CREATE TABLE modify_test(
id serial PRIMARY KEY,
data text,
created_by text default SESSION_USER,
created_at timestamp default
CURRENT_TIMESTAMP,
last_changed_by text default SESSION_USER,
last_changed_at timestamp default
CURRENT_TIMESTAMP
);
