How many times have you needed to archive a piece of information, say sales quotes, without completely getting rid of it? You don't want a sales dashboard to be cluttered with old quotes but at the same time, you will want to run reports on how many sales quotes get converted on a regular basis. So you have to archive this info, but how?
On the quotes table you could just add a simple boolean field named archive with a default value of 0. You update all places in your codebase that retrieve quotes and ensure that only quotes with archive set to 0 are retrieved. The pros of doing it this way is it is pretty simple. If you have good separation between your database layer and application layer, then you should only have to filter the quotes in the persistence layer.
Archive Date Field
Another possibility is to add a deleted_at column instead of an archive column. This would be identical to an archive column with the exception of also storing the date in which the record was archived. Again it is a pretty simple solution and it has the benefit of giving you the date the record was archived.
Let me point out an issue with using archive and deleted_at columns. Assume you have a employee table with the following schema: - id (auto increment) - username (unique) - email (unique)
An employee moves from one company to another which requires the employee to be archived. Note that in this example we don't want any of the employees history or relationships to be transferred to the their new company, hence why the employee needs to be archived. The employee is archived meaning the archive or deleted_at column is updated. Now the employee tries to sign up to a new company. Do you see the issue? The employee can't sign up with the same email address due to the unique email address constraint*.
*I would like to point out that one way of fixing this issue could be to remove the unique constraint from the email field. I don't think this is a good solution because it means one person could sign up multiple times with the same email address.
Another issue is that if your application layer and persistence layer does not have good separation of concerns, then you will have to add the archive and deleted_at filter in multiple places. You also need to remember to add this filter when creating new queries.
Archive Table using Triggers
Triggers are a useful way of managing databases. They allow you to maintain the integrity of the data being stored without having to write any application layer code. They are also very good at creating audit trails. If you have ever archived a record by setting an archive field to 1 or saving a Datetime to a deleted_at, then you will understand that the application code needs to be written for filtering out the archived records. Using triggers we can instead save the record into a identical archive table before we delete it. No filtering needed. You can still access the records by retrieving them from the archived table. One big plus of using triggers is that you now have an audit trail that can easily be queried.
There are some downsides to using triggers in this scenario. One is that it is not clear from the application layer that triggers are present. To solve this issue, in your persistence layer you can comment saying that any deletes will fire a trigger. Another downside is that you are now tying your implementation to the database.
How to test database triggers
Database triggers can be tested like any other integration test that interacts with the database, using the Arrange Act Assert testing pattern. Arrange in this scenario means setting up the database state so that the trigger can be fired. Act would be executing an Insert, Update or Delete statement that would cause the database trigger to be fired. Assert would be to check that the database trigger has inserted, updated or deleted the data it was supposed to.