What are the different ways to keep track of active and archived data?
The two options that spring to mind avoiding triggers are:


Flag the deleted rows as such with an extra field, and update all your existing code to deal with it. This is "simple" but will touch a lot of your code so can be a bug source. Delete is not delete, mearly "set is_achived flag".
Flag the deleted rows in the table, and have views for active and archived data. This avoids changing existing code as you can give the combined table a new name (users_all) and the view of active users (SELECT &lt;fields&gt; FROM users_all WHERE is_archived=0) the name of the old table. Code such as audit reports simple need to query users_all directly. You will need to run a few tests to make sure mysql's query planner handles this efficiently though - I've had it work well is MSSQL but don't use mysql myself so have no expectations one way or the other about it's QP's intelligence.
Separate tables for active and archived. The delete operation then becomes an insert into archived and a delete from active (be sure to wrap this in an explicit transaction so if there is an error half way through you don't lose records completely or end up duplicating them). For code that needs to see both subsets, either UNION directly in the views/procs or have a view that is a UNION of the two tables and use that. This is likely to be less efficient than the above for those code paths that do need to deal with both subsets, but will not affect other areas of the codebase so may be preferable. One key disadvantage with this option is not being able to use the two tables in FK relationships - you can get around this by adding a third table containing just the identifiers for each row, then all tables that contain links to users (including the active users and archived users tables) would use that as the FK source - the only extra work in your business logic as a result would be to create that row before creating the row in the users table.


Triggers can be very powerful, but you need to design them very carefully as they can be a performance problem if not, and of course silent things happening can be a problem when bugs to arise as it can be difficult to see everything that is going on at first glance. they can greatly simplify some processes though, creating easier to maintain structures as a result, and sometimes having a bit of structural logic in the DB is far more efficient than having the next layer up deal with it, so never discount them out of hand. In this instance though, I'd stick with one of the simple boolean flag options.