I am using MYSQL with PHP for a Student database.
Whenever I update a Student's info I would like to keep a record of the previous values.
Right now I have a trigger set up to insert the entire update into a history table which is a duplicate of the Students table with a few added columns: Action, and timestamp.
before update
on Students
for each row
INSERT INTO StudentHistory SELECT 'update', NULL, NOW(), d.*
FROM Students AS d WHERE d.ID = NEW.ID;
However, since this table has about 20 fields I'd rather not have that much duplicate data if just one field was updated.
Is there a way to insert only columns that actually changed into the history table?
If not, is there a way to compare the rows in the history table for each student to see which columns actually changed?