Re: audit information

On Mon, 25 Apr 2005 11:01:51 -0400, Sean Davis wrote
> On Apr 25, 2005, at 10:41 AM, Keith Worthington wrote:
>
> > Hi All,
> >
> > I need to add an audit trail to my application.  I thought that
> > I would add the user id and timestamp to the tables where I need
> > to track 'last modified by...'.  Currently there is a table
> > tbl_employee that I would like to be able to cross reference to
> > in order to obtain the user's real name when  necessary.
> >
> > MYDB=# \d tbl_employee
> >            Table "tbl_employee"
> >      Column     |         Type          | Modifiers
> > ----------------+-----------------------+-----------
> >  id             | character varying(20) | not null
> >  first_name     | character varying(15) | not null
> >  middle_initial | character(1)          |
> >  last_name      | character varying(20) | not null
> >  inactive       | boolean               | not null
> >  email          | character varying(64) |
> > Indexes:
> >     "tbl_employee_pkey" PRIMARY KEY, btree (id)
> >
> > Is there a way to obtain the postgres user id or must I store
> > the output of session_user in the tables?
>
> Not sure on this one.  What is the "id" in your table?  Is it the
> postgres username?
>
> > Are there some existing functions and or tools for
> > implementing audit trails?
>
> Would a trigger on the table that you want to audit do the trick?
>
> Sean

The tbl_employee.id column contains data from the financal software.

Yes, I believe a BEFORE trigger would do the trick nicely.  I can
simply insert the approriate data on the way by.

My question is do I have to use session_user which returns type name
or is there a function that returns the usesysid which is type int4?

Regardless of whether the data I use is int4 or name I need to add a
column to tbl_employee for cross referencing to the employee's real
name.

Kind Regards,
Keith