hi all,

i have a table (not my design) with a lot of fields and users have access
to insert/update/delete record from this table. is there a way that mysql
can log all transactions who change what on this table??? or do i have to
create a seperate table to keep track the changes?

for example: someone changed the price from 2.00 to 2.50 on price

this needs to be done for the purpose of auditing.

i found some docs on mysql.com, but mostly for query log & server log.


thanks,
t. hiep

On Wed, Mar 12, 2008 at 12:48 PM, Hiep Nguyen <hiep@ee.ucr.edu> wrote:
> hi all,
>
> i have a table (not my design) with a lot of fields and users have access
> to insert/update/delete record from this table. is there a way that mysql
> can log all transactions who change what on this table??? or do i have to
> create a seperate table to keep track the changes?
>
> for example: someone changed the price from 2.00 to 2.50 on price
>
> this needs to be done for the purpose of auditing.


A few years ago, I worked for a big financial that had an Oracle
database shared by some 500 applications. Each application used it's
own credentials. What we did did keep track was to add a field
'MODIFIED_BY' to each table to be audited; the field was a varchar
wide enough to hold any username. We then added triggers to those
tables to update the 'MODIFIED_BY' field on every insert or update.
It did not give us a continuous log of changes but we at knew who was
responsible for the current state of any audited record.

I have extended this principal and used triggers to keep keep a
timeseries of every record changed on the system; it's a fair bit of
work and can eat up storage like mad (depending on your
rate-of-change) and is totoal overkill for all but the most sensitive
cases.

- michael dykman

--
- michael dykman
- mdykman@gmail.com

- All models are wrong. Some models are useful.