How to store users action (finished queries) in a table?

Question

So this is my situation: I have an admin user, that can add,delete and edit other users. I'm looking for an a solution how to record the admins action. Should i compare the table, that is affected before and after the query is done and how can i store the difference in another table?

This is the table that would be afected:

TABLE `t_user` ( 
  `user_id` int(11) NOT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` char(32) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `active` tinyint(1) NOT NULL,
  `current_desk_book` int(11) NOT NULL,
  `last_login` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Lets say the admin tries to delete a user with id=3, the query would be

"DELETE FROM t_user WHERE user_id= 3"

These are the deleted user data:

(3, 'user3', 'fa6daddc77ac9b5ee42ffd31e7d6e014', 'user3@mail.bg', '2016-09-08 11:29:38', 1, 1, NULL)

Im looking to store "Deleted " + the deleted users data in another table.

Answer 1

Two ways : Simple solution, better have a flag as "deleted" and mark it to "1" admin deletes it. This is soft delete method.

Hard Delete: Create a log table similar to base users table. Have a trigger, when delete happens. store it into another table.

 
    CREATE TRIGGER log_user_delete AFTER DELETE on users
    FOR EACH ROW
    BEGIN
    INSERT INTO users_log  (user_id, username, etc, deletedby_newfield, deletedtime_newfield)  values (old.user_id, old.username, '1', NOW());
    END