Hi all,
I've got my database on which I'm building a Java application. Since I'd like
to store information about who inserted and updated a specified record, I
placed in my main tables a few additional fields to store such information:
CREATE TABLE myTable(
....
insertUser varchar(30),
insertDate date,
updateUser varchar(30),
updateDate date,
FOREIGN KEY insertUser REFERENCES users(username),
FOREIGN KEY updateUser REFERENCES users(username)
);
where 'users' is a table that stores the usernames allowed to connect thru my
application. This solution works, of course, but as you can imagine is quite
boring to insert into each sql statement values for this additional fields,
and even if I can set-up default values, this can be error prone.
After a while, in order to get a little debug level on my application, I
started logging into a table the sql query that each user/client has
executed. This to understand why and which query can be wrong, which client
version has issued it, from which, etc. So I've got a kind of backtrace of
changes in the database disregarding the above additional fields. Even if
this log is more difficult to consult than the above additional fields (I
need to search within the sql statement string), I was wondering to remove
such additional fields.
Here comes my question: how can I catch user changes to each record in the
database without be bored with user/date details? Anyone can suggest me a
smart solution and/or database design? Anyone has already found such kind of
problem?
Thanks,
Luca
