Re: Enabling Audit Trail in Postgres

You could use some rules as well. I use two PL/pgSQL triggers to
set/update a version number on rows for insert/update. There are some
rules that insert a copy of the old row to a history table on update or
delete. Another rule prevents this history table from being updated. I
still allow deletes to the history table but only because it has to be
emptied occasionally.

For example:

CREATE SEQUENCE AuditSeq;
CREATE TABLE Audit (
AuditSeq INTEGER NOT NULL,
Version SMALLINT NOT NULL,
UpdatedBy VARCHAR(16) NOT NULL,
Created TIMESTAMP NOT NULL,
Modified TIMESTAMP NOT NULL
);

CREATE RULE AuditUpd0 AS ON UPDATE TO Audit DO INSTEAD NOTHING;
CREATE RULE AuditIns0 AS ON INSERT TO Audit DO INSTEAD NOTHING;
CREATE RULE AuditDel0 AS ON DELETE TO Audit DO INSTEAD NOTHING;

CREATE TABLE OrgPeople ( ... ) INHERITS (Audit);
CREATE TABLE .... ( ... ) INHERITS (Audit);

View most recent modifications regardless of table
CREATE VIEW AuditList AS
SELECT pg_class.relname,
Audit.Version,
Audit.UpdatedBy,
Audit.Created,
CASE WHEN Audit.Created = Audit.Modified
THEN NULL
ELSE Audit.Modified
END AS Modified
FROM pg_class, Audit
WHERE Audit.table_oid = pg_class.oid
ORDER BY Audit.AuditSeq;

And then of course just write two triggers to set the audit attributes on
INSERT and UPDATE.

-- Generic auditing
--
--
-- DROP FUNCTION AuditUpd();
CREATE FUNCTION AuditUpd() RETURNS OPAQUE AS '
BEGIN
NEW.Created = OLD.Created;
NEW.Modified = current_timestamp;
NEW.AuditSeq = nextval(''AuditSeq'');
RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

-- DROP FUNCTION AuditIns();
CREATE FUNCTION AuditIns() RETURNS OPAQUE AS '
BEGIN
NEW.Created = current_timestamp;
NEW.Modified = NEW.Created;
RETURN NEW;
END;
' LANGUAGE 'plpgsql' WITH (isstrict);

CREATE TRIGGER OrgPeopleIns BEFORE INSERT ON OrgPeople
FOR EACH ROW EXECUTE PROCEDURE AuditIns();
CREATE TRIGGER OrgPeopleUpd BEFORE UPDATE ON OrgPeople
FOR EACH ROW EXECUTE PROCEDURE AuditUpd();

You can also get the versioning history by adding something like

CREATE TABLE OrgPeople_OV ( ... same def as OrgPeople except w/o the
constraints ... ) INHERITS (Audit)

CREATE RULE OrgPeopleVer2 AS ON UPDATE TO OrgPeople_OV DO
INSTEAD NOTHING;

CREATE RULE OrgPeopleVer0 AS ON UPDATE TO OrgPeople DO
INSERT INTO OrgPeople_OV SELECT OLD.*;
CREATE RULE OrgPeopleVer1 AS ON DELETE TO OrgPeople DO
INSERT INTO OrgPeople_OV SELECT OLD.*;

So now that's a complete history for a table.

Joshua b. Jore ; http://www.greentechnologist.org ; 1121 1233 1311 200
1201 1302 1211 200 1201 1303 200 1300 1233 1313 1211 1302 1212 1311 1230
200 1201 1303 200 1321 1233 1311 1302 200 1211 1232 1211 1231 1321 200
1310 1220 1221 1232 1223 1303 200 1321 1233 1311 200 1201 1302 1211 232
200 1112 1233 1310 1211 200 1013 1302 1211 1211 1232 201 22

On Sat, 1 Jun 2002, V R wrote:

> Enabling auditing in oracle will let us keep track of any updates,inserts or deletes on tables. I would like to know if
> the same can be done in postgres, other than the use of Triggers.
>
> -Thanks
> Radha
>
> ______________________________________________________________________________________________________________________________
> Chat with friends online, try MSN Messenger: Click Here
>
>