Applictaion data Logging
От | Jesus Sandoval |
---|---|
Тема | Applictaion data Logging |
Дата | |
Msg-id | 3DF5492E.68E749AB@mzt.megared.net.mx обсуждение исходный текст |
Ответ на | Cluster Database ("Al-Karim Bhamani (LCL)" <ABhaman@ngco.com>) |
Список | pgsql-admin |
Hi, I will do this programatically but I wished to post this question just to know if somebody have any suggestion, because I am new to server side functions or rules or trigger programming. I want to log any data change in one application, for audit reasons, I created a table for that purpose the table has the next fields: id SERIAL -- Primary key tablename VARCHAR(40) -- Table name that changed fieldname VARCHAR(40) -- fieldname that changed in the table user VARCHAR(16) -- User that made the change timedate TIMESTAMP WITHOUT TIME ZONE -- Timestamp of change keyvalue TEXT -- The key value of the record that changed oldcontent TEXT -- the field's content before the change newcontent TEXT -- the field's new content I'm programming in Java and using JDBC. Notes: 1) The program knows the user id that made the change (postgres is not aware of this user id, because there is one user that connect from the application, after the connection to postgresql, the applications validate the user id from a postgresql table, if it is ok, the program continues). 2) In order to know wich record is updated (or inserted or deleted) the keyvalue is saved in the log record. 3) Maybe newcontent is redundant, because I can check that value in the actual table, so maybe it can be dropped from this table. As I said, I plan to do this log from the program, but I think it can be done with Triggers or rules, but I don't know if it is possibly or how to do the following. 1) How to pass the user id from the application to postgres. 2) How to save the tablename of the table that is modifying in the logtable, the same for the fieldname. 3) On one update there may be 2 or more fields (columns) changing, so I need to store 2 or more records to the logtable, How can I do that. 4) to get the key value of the changed record. One query can change many records and many changes. 5) Which is best option: Triggers, Rules or application logic???? (none of them????) Thanks Jesus Sandoval
В списке pgsql-admin по дате отправления: