CODEWITHSUNDEEP
phpmysql
Kama
Kama

Reputation: 193

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

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?

Upvotes: 3

Views: 1635

Answers (2)

Sebas
Sebas

Reputation: 21522

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. [...]

Upvotes: 2

goFrendiAsgard
goFrendiAsgard

Reputation: 4084

I think no. But you can do a little trick. For example, before you delete a record in table A, select all record in other tables which is related to the deleted record.

   $sql = "SELECT Id FROM b WHERE b.a_id ='$deleted_A_Id'";
   //delete physical file and any other things
   $sql = "DELETE FROM A where A.id = '$deleted_A_Id'";
   //just exec sql the normal way

Upvotes: 1

Related Questions