One of my coworkers came up with this:
http://foremanbob.*************/2008/...-in-mysql.html

What are your thoughts about doing this in a MySql environment, or using the methods he shows?

first of all, and most importantly, i am flattered that you would ask me for my opinion

secondly--and i hope this isn't too much of a disappointment-- i have no idea what this would be used for

i wonder if i could appeal to your mercy and ask for forgiveness for my total ineptitude at understanding what you DBAs get so excited about

I like the general idea, but I have some reservations.

First of all, this approach relies on MySQL triggers and the blog doesn't mention if the application tests to see if this MySQL engine accepts the declaration of triggers or if it tests to see if a declared trigger will actually fire and do what you expect. This trips me up on a regular basis, since different MySQL versions may not accept the declaration of a trigger, and at least some of the MySQL versions that I have to support will allow you to define a trigger that they will never execute. Programs can assume much faster than people can, and we all know what happens when people assume!

If I were to use this method of archiving, I'd suggest two changes. First, I'd have the shadow table created by one executable, and the data copy from production to shadow done by a second executable. Second, I'd use a date column for notating the deletion because that would offer me a great deal more useful information for what I perceive to be very little cost.

In general, I like the idea though.

-PatP

With my method, each production table holds a modified timestamp and records the connection that modified it. The archive table would have the same columns, making a timestamp column for deletion redundant. But it appears my colleague left this off of his post.