How can I use a Postgres Triggers to store changes

Question

Is it possible using postgres and triggers to create update and insert sql statements of changes that have happened to a table and log them to a file for later execution.

This is only to be used temporally so just something quick and dirty.

Regards,

/James

Answer 1

The link below should point you in the right direction.

http://developer.postgresql.org/pgdocs/postgres/sql-createtrigger.html

Depending on what you want to do, it probably is better to turn on logging.

Answer 2

example of an audit trigger from http://www.postgresql.org/docs/8.4/static/plpgsql-trigger.html

CREATE TABLE emp (
    empname           text NOT NULL,
    salary            integer
);

CREATE TABLE emp_audit(
    operation         char(1)   NOT NULL,
    stamp             timestamp NOT NULL,
    userid            text      NOT NULL,
    empname           text      NOT NULL,
    salary integer
);

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Answer 3

Do you actually need the audit log of queries stored in a table? The easiest way to get a file with all the queries that have been executed is to use postgresql's built-in logging.

In your postgresql.conf (usually in the $PG_DATA dir), set the following options appropriately:

log_directory '/path/to/log/dir'
log_filename = 'filename.log'
log_statement = 'mod'

That last option makes it log all the INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM statements.

More details in the Postgres docs: http://www.postgresql.org/docs/current/static/runtime-config-logging.html

Answer 4

PostgreSQL Table Log by Andreas Scherbaum is a Postgresql extension that uses a trigger to log any INSERTs, UPDATEs and DELETEs on a specific table into another table.

The usage is easy: you create a second table which has the same format like your table you want to keep an eye on. Plus you need some additional columns to maintain the logged data.

The second part of tablelog is able to restore the state of the original table or of a specific row for any time in the past.

I haven't tried it myself, but it's supposedly working.

There's also slides from a talk about tablelog, but I can't post a proper link here due to some stackoverflow antispam weirdness :) (http :// andreas.scherbaum.la/writings/tablelog.pdf).

http://pgfoundry.org/projects/tablelog/

http:// andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html