brunns brunns - 2 months ago 13
MySQL Question

What are the options to automatically save certain rows from a table when they are inserted?

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.

Answer

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 ;