brunns brunns - 1 year ago 74
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 Source

You can use triggers:

Code and after insert trigger as the example in

Something like this:


CREATE TRIGGER Back_Analysis_Data 
   ON Analysis_Data FOR EACH ROW


   INSERT INTO Saved_Data ( Col1, Col2 )
   VALUES ( NEW.Col1, NEW.Col2 );

END; //

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download