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