Re: table version history

On Sun, 8 Apr 2001, Joel Burton wrote:

(oops! I sent it before I included the scripts. My apologies)

On Sun, 8 Apr 2001, Tom Strickland wrote:

> I'd like to provide users with history on important tables, so that
> they can go back and examine changes. This is partly to do with the
> UK's Data Protection Act, which states that an organisation must keep
> a log of all changes to information kept on a user. I'd like to have a
> mechanism of being able to print a report on one of our clients that:
> 1) prints all current info on user (easy)
> 2) prints all changes to that user's info (not so easy).

You could add a trigger onto the table so that all updates are inserted
into a log table.


CREATE TABLE person (fname text, lname text);

CREATE TABLE person_log (changeid serial, fname text, lname text, op text, chgat
timestamp default current_timestamp, chgby varchar(32) default current_user);

CREATE FUNCTION person_log_func () returns opaque as '
BEGIN
  IF TG_OP = ''DELETE'' THEN
    INSERT INTO person_log (fname,lname,op) values (OLD.fname, OLD.lname,TG_OP);
  ELSE
    INSERT INTO person_log (fname,lname,op) values (NEW.fname, NEW.lname,TG_OP);
  END IF;
  RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER person_log_chg  after update or insert or delete on person
for each row execute procedure person_log_func();


INSERT INTO person VALUES ('joel','burton');
UPDATE person SET fname = 'pup';
DELETE FROM person;

SELECT * FROM person_log;

 changeid | fname | lname  |   op   |         chgat          | chgby
----------+-------+--------+--------+------------------------+-------
        1 | joel  | burton | INSERT | 2001-04-08 14:44:25-04 | joel
        2 | pup   | burton | UPDATE | 2001-04-08 14:44:25-04 | joel
        3 | pup   | burton | DELETE | 2001-04-08 14:44:25-04 | joel


HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington