luinalin has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

Our marketing group makes changes to a fairly simple table (5 cols) in a postgres db via a cgi.

In a nutshell I want to disaply an html history of who made what changes to the table content, and am wondering if there's some module out there I can leverage for this.

I need to show more than, Bob made some changes on date X, I need to show, Bob deleted this, added this, modified this. etc.

Later I'd actually like to implement this on more than one cgi, that changes content.

Anything come to mind that could be tasked for this, or a simple way to do this?

I feel like there must be somethign already that I don't know about.

thank you!
Lin

Replies are listed 'Best First'.
Re: Tracking changes to a db table
by kyle (Abbot) on Feb 07, 2007 at 14:59 UTC

    This could be done entirely in the database with a trigger. I haven't actually used triggers before, but I think you can:

    1. Create a table to hold the auditing data you want.
    2. Create a trigger for UPDATE and DELETE. It will write into the auditing table.
    3. Your display would then read out of the auditing table.

    Here's PostgreSQL 7.4 Trigger documentation (I don't know what version you're using).

[reply]
Re: Tracking changes to a db table
by fmerges (Chaplain) on Feb 07, 2007 at 15:15 UTC

    Hi,

    kyle is right, you can do this with triggers. You can see an example here about using Perl and PostgreSQL.

    I don't know your problem domain, but you could also think about using some revision control system

    Regards,

    fmerges at irc.freenode.net
[reply]
Re: Tracking changes to a db table
by fenLisesi (Priest) on Feb 07, 2007 at 16:09 UTC
    You could create an action_history table and update that, within a transaction, every time you do something that you consider loggable.
[reply]

Back to Seekers of Perl Wisdom