How do I keep track of just deleted rows in a table on MySQL?

Question

I have foreign key constraints set on my MySQL tables that cascade on delete. I need to keep track of all the other tables and rows affected when I delete a user because there are physical files involved that must also be deleted. Is there a way to log this?

Answer 1

Well, by adding a trigger on each table that you'ld like to track you could easily:

• feed an audit table
• run a background job to delete the corresponding files

Like Mitch said, it also depends on you, what you're wanting to do exactly.

For instance you could also want to keep the rows for history purpose. In this case you might implement your own rows "suppression" by using a "deleted" flag in the tables, so you would put it to 1 whenever you want to remove a row. [...]