0

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.

brunns
  • 35
  • 8

2 Answers2

2

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 ;
espino316
  • 422
  • 3
  • 8
1

I advise against triggers and stored procedures as they don't scale. And creating a second table is just extra maintenance and storage overhead for MySQL.

The best practice for this is to implement soft delete on your client side code. For this to work, you will need to add an additional column to your Analysis_Data called "soft_delete" like this:

ALTER TABLE Analysis_Data ADD COLUMN soft_delete TINYINT NOT NULL DEFAULT 0 ADD KEY soft_delete_key (soft_delete)

Then your queries to read non deleted data select * from Analysis_Data where soft_delete != 0 limit 10

to read deleted/non deleted data together select * from Analysis_Data limit 10

Arya
  • 2,037
  • 13
  • 12
  • Thanks a lot for introducing the term soft delete. This is the first time I heard about soft delete :) Googling a bit on soft delete I found this and it seems in my scenario, soft delete would probably not be a good idea. But your answer eventually lead me to my answer. Thanks again! – brunns Oct 03 '16 at 23:38
  • The downside is the potential for a lot of dogged rows and we all know our queries (ok other people's queries) often are complete table scans or "partial off a where" via [explain](https://www.sitepoint.com/using-explain-to-write-better-mysql-queries/) . In fact it could be said that most op questions would demonstrate a horric schema pitted against the go-to queries they actually have. So I would dog the rows to another archive table. For smaller tables soft_deletes are great – Drew Oct 03 '16 at 23:52