I have a table named Analysis_Data that stores metrics data generated by a software agent based on code analysis. The agent inserts the metrics into the Analysis_Data table automatically and it deletes some metrics from the previous analysis. However, I want to preserve all data from the Analysis_Data table.
Is there any mechanism available in mysql that would automatically copy the newly inserted rows from the Analysis_Data table to another table Saved_Data so that I have the data available even if the agent deletes rows in the later analysis cycles?
I am using mysql version 14.14 distrib 5.7.13.
You can use triggers: http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html
Code and after insert trigger as the example in https://www.techonthenet.com/mysql/triggers/after_insert.php
Something like this:
DELIMITER // CREATE TRIGGER Back_Analysis_Data AFTER INSERT ON Analysis_Data FOR EACH ROW BEGIN INSERT INTO Saved_Data ( Col1, Col2 ) VALUES ( NEW.Col1, NEW.Col2 ); END; // DELIMITER ;