pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Markus Wagner <wagner@imsd.uni-mainz.de>
To: pgsql-sql@postgresql.org
Subject: audit trail and system catalogs
Date: Wed, 6 Jun 2001 17:15:20 +0200
Message-ID: <01060617152003.30292@mws> (raw)

Hi,

we strongly need to implement an audit trail as a prerequisite for 
clinical trials, that is a functionality which records any change of any 
data item in a database into one single table containing these events.

I found that one could use rules for this, but this would require one rule 
for each attribute of each table ("...ON UPDATE ... WHERE old.attr <> 
new.attr...").

My first question:
Could one reduce the rule set with rules for tables, e. g. one rule which 
fires whenever *some* attribute of a table changes? Would there be a 
possibility to reference the changed attributes in the action clause?

Assuming that one really needs one rule for each attribute I would like to 
create a script which generates all rules for all attributes of all tables 
for a database.

The problem is, how to get the names of all tables and their attributes? I 
looked into the system tables ("pg_*"), but there were many tables and 
many attributes for *my* tables, and I did not figure out how to 
distinguish my tables and my attributes from the other ones. None of the 
columns in pg_class and pg_attribute seems to give information on wether 
the item is system or user defined.

My second question:
How can I loop to all of *my* tables and *my* attributes, ignoring system 
tables and system generated attributes within my tables?

Thank you very much for any hint,

Markus



view thread (4+ messages)  latest in thread

Message-ID: <01060617152003.30292@mws>
Permalink:  ../01060617152003.30292@mws/
Also on:    postgresql.org/message-id/01060617152003.30292@mws

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: wagner@imsd.uni-mainz.de
  Subject: Re: audit trail and system catalogs
  In-Reply-To: <01060617152003.30292@mws>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox