Re: log changes

["Michael Dykman" <mdykman@xxxxxxxxx>]

On Wed, Mar 12, 2008 at 12:48 PM, Hiep Nguyen <hiep@xxxxxxxxxx> 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@xxxxxxxxx

 - All models are wrong. Some models are useful.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=royale@xxxxxxxxxx