audit trail and system catalogs
От | Markus Wagner |
---|---|
Тема | audit trail and system catalogs |
Дата | |
Msg-id | 01060617152003.30292@mws обсуждение исходный текст |
Ответы |
Re: audit trail and system catalogs
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: