How to Track any changes made on Database Table???

If you want to track any changes made on your database table, So here is the best solution to track changes made on the database table.

Trigger is a sql code which runs just before or just after an INSERT, UPDATE or DELETE events occur on a particular database table, Triggers have been supported in MYSQL since version 5.0.2

Create your Table in Database:

CREATE TABLE `task` (
  `ID` int(11) NOT NULL,
  `user_name` varchar(25) NOT NULL,
  `date` date NOT NULL,
  `t_name` varchar(25) NOT NULL,
  `partner` varchar(11) NOT NULL,
  `director` varchar(25) NOT NULL,  `duration` float NOT NULL,`task` varchar(255) NOT NULL,`status` varchar(25) NOT NULL,                                            
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `task`
  ADD PRIMARY KEY (`ID`);

ALTER TABLE `task`
  MODIFY `ID` int(11) NOT NULL AUTO_INCREMENT;

In the above code, we have created the task table which has the user_name, date, t_name, partner and director column. If any event occur in the above table then, it will fire the trigger and store the record in the table mentioned in trigger query.

Create another table, where you want to track the changes made on the table:

CREATE TABLE `task_log` (
  `ID` int(11) NOT NULL,
  `user_name` varchar(25) NOT NULL,
  `log_action` varchar(25) NOT NULL,
  `log_timestamp` date NOT NULL,                                             
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The above table, is used for storing the data when any event occur in the task table.
User_name stores the name of the user, Log_action stores those events which occur on the table (insert, update, and delete) and lastly log_timestamp stores the data and time when the specified event occur on the task table.

Fire a Trigger Query in your PhpMyadmin SQL Tab:

CREATE TRIGGER after_update 
                 AFTER UPDATE ON task
                 FOR EACH ROW 

                 INSERT INTO task_log
                 SET log_action = "update",
                 user_name      = NEW.user_name,
                 log_timestamp  = NOW()

If you want to display all the columns then create a new column in task_log and also add row in the trigger Query.

If you have any issues, then feel free to write in the comments down below... will surely get back to you.

Stay tuned for more coding!!

Thank you....