Search code examples
mysqlrecord

Mysql setting a record as deleted or archive

Is there any way to omit some records in mysql select statement and not deleting them? We can easily add a column for example deleted and set it to 1 for deleted ones and keep them but the problem is that we have to put where deleted = 1 in all queries. What is the best way to keep some records as an archive?

Solution

  • I don't know how many tables you have and how much data you want to store, but a solution could be this one:

    • You create a tblName_HIST table for each the tables (tblName) you want to keep the virtually deleted data
    • Optional: Add a DELETED_DATE column to keep track of the date the record was deleted.
    • You add a Trigger on the tblName tables that AFTER DELETE statement INSERT the record in the tblName_HIST table.

    This will allow you to keep the Queries and the DB tables made since now without modify them that much.