Re: Table audit system - Mailing list pgsql-general

I use a modified form of option 3 with an ON UPDATE RULE.... the update rule
copies the row to an inherited table...

CREATE TABLE dm_user (

       id SERIAL NOT NULL PRIMARY KEY,

       lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id),
       dm_user_address INTEGER NOT NULL DEFAULT 0,
       dm_user_email INTEGER NOT NULL DEFAULT 0,

       f_name VARCHAR(50) NOT NULL,
       m_name VARCHAR(50) NOT NULL,
       l_name VARCHAR(50) NOT NULL,

       uname VARCHAR(20) NOT NULL,
       upwd VARCHAR(20) NOT NULL,
       pwd_change_reqd BOOLEAN DEFAULT FALSE,
       login_allowed BOOLEAN DEFAULT TRUE,
       lost_passwd BOOLEAN DEFAULT FALSE,

       create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
       change_dt TIMESTAMP NOT NULL DEFAULT NOW(),
       change_id INTEGER NOT NULL DEFAULT 0,
       active_flag BOOLEAN NOT NULL DEFAULT TRUE

) WITH OIDS;


CREATE TABLE dm_user_history (

       history_id SERIAL NOT NULL PRIMARY KEY,
       hist_create_dt TIMESTAMP NOT NULL DEFAULT NOW()

) INHERITS (dm_user);

CREATE RULE dm_user_upd_history AS ON UPDATE TO dm_user DO INSERT INTO
dm_user_history SELECT * FROM dm_user WHERE id = old.id;

CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE
dm_user SET active_flag = FALSE WHERE id = old.id;



"Scott Frankel" <leknarf@pacbell.net> wrote in message
news:bd02bff5561d8b271301ba10bafca105@pacbell.net...
>
> Is there a canonical form that db schema designers use
> to save changes to the data in their databases?
>
> For example, given a table with rows of data, if I UPDATE
> a field in a row, the previous value is lost.  If I wanted to
> track the changes to my data over time, it occurs to me that
> I could,
>
> 1) copy the whole row of data using the new value, thus
>      leaving the old row intact in the db for fishing expeditions,
>      posterity, &c.
>      -- awfully wasteful, especially with binary data
>
> 2) enter a new row that contains only new data fields, requiring
>      building a full set of data through heavy lifting and multiple
> queries
>      through 'n' number of old rows
>      -- overly complex query design probably leading to errors
>
> 3) create a new table that tracks changes
>      -- the table is either wide enough to mirror all columns in
>          the working table, or uses generic columns and API tricks to
>          parse token pair strings, ...
>
> 4) other?
>
> Thanks
> Scott
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


"josue" <josue@lamundial.hn> wrote in message
news:424177B9.8000600@lamundial.hn...
> Hello list,
>
> I need to define an audit system that would be easyli include or exclude
> certain tables, the process is a purchase order where many users changes
> the info in diferent ways, the requerimient is to log the stamp and user
> of the change on a table and additionaly log a snapshot of the the order
> at the time it was change, that must include any child table too,
> generally the order document includes the order header main table, the
> order detail child table, the order costs child table and the order
> comment history child table. So given the need to log a full snapshot not
> only the change of a column I ask you for ideas or suggestion to get this
> properly done in Postgresql.
>
> Thanks in advance,
>
>
> --
> Sinceramente,
> Josu� Maldonado.
>
> ... "Toda violaci�n de la verdad no es solamente una especie de suicidio
> del embustero, sino una pu�alada en la salud de la sociedad humana." Ralph
> Waldo Emerson. Fil�sofo, ensayista, poeta y pol�tico EE.UU.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>