Thread: table version history

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).

I'm not asking for anything fancy (such as the ability to roll back
records). Has anyone else tried this?

Thanks,

tom

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.





 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
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

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

Hmmm... pretty much exactly what I want! I suppose that in the long
term it would be nice if we could change things so that only changed
information is written in, but that would require extra handling to
reconstitute the record and our db is not going to get so big that
space becomes a problem.

Thanks,

Tom

On Sun, Apr 08, 2001 at 02:48:49PM -0400, Joel Burton wrote:
> 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