Hello!
My goal is to create 'shadow' tables for all of the tables in my database. For instance:

'Customers' table would have a shadow of 'Customers_Arc'.
'Orders' table would have a shadow of 'Orders_Arc'.

Whenever a row gets inserted or updated, a new row gets added to the corresponding _Arc table with the the row as it is when inserted or updated. When a row is deleted, a row is inserted to the _Arc table with a 'Deleted' column (bit flag) turned on.

Ideally I would use a script to generate the _Arc table(s) and triggers. Does anyone know if something like this is out there and available? Possibly a MyGeneration template?

Thanks.

I don't know of anything like this, but if all that you're trying to do is have copies of every table, this is very easy. The best thing to do is just do daily backups. I think this may be less strain on your server. Otherwise, if you insist on having instant backups, just modify your scripts. Depending on how complex your scripts are, it seems that all you would have to do is copy your queries and change the table names. Does that make sense?

Hi,
Your problem is a pretty std one. Just taking backups of the tables is not what you want (I think).

I have often been approached to solve this problem - Mostly for audit firms that would like to track the changes to tables at a detailed level. (Even access of data)

Although there are many approaches to this the best - and most sustainable way is to employ a SINGLE "go-through" script for access / write to etc. to your databases.

At the same time simply insert the SQL statement being executes into a table. The results are that you save each and every SQL statement in a separate table (s_log).. after execution in the target table. This is usually done through a data logic layer (attached in PHP code).

This is very useful since it is a superb backup. If the statements (excluding the SELECT ones) are re-executed in a sequential way a table may be re-created from scratch. - This table (s_log) is backed up regularly on all my systems and in some even trickled to another database for posterity and detailed reporting.

The script for the table structure and sample code in PHP is in the attachment .. the results also..
Although the code is in PHP (oo code) the comments are simple to read and should be self-explanatory..

If you think this may solve your problem let me know.. I will expose a detailed doc on it on my site. GOOD LUCK!

Wayne

Thanks for the replies.
I wound up creating an app for myself that will meet my need. Feel free to reply with suggestions on how to improve it!

You can read about and download it here: http://foremanbob.*************/2008/...-in-mysql.html.

Good .. Go well