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.
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;
Of course, this works only in a table that has the correct fields:
CREATE TABLE modify_test( id serial PRIMARY KEY, data text, created_by text default SESSION_USER, created_at timestamptz default CURRENT_TIMESTAMP, last_changed_by text default SESSION_USER, last_changed_at timestamptz default CURRENT_TIMESTAMP ); CREATE TRIGGER changestamp BEFORE UPDATE ON modify_test FOR EACH ROW EXECUTE PROCEDURE changestamp();
Now, let's take a look at our newly created trigger:
postgres=# INSERT INTO modify_test(data) VALUES('something'), INSERT 0 1 postgres=# UPDATE modify_test SET data = 'something else' WHERE id = 1; 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:28:31.937196
18.224.66.196