What is the most efficient way to store recent changes for a mysql table

Question

I have a table named Warehouse for my database, it has Warehouse_idWarehouse and Warehouse_name as primary keys. What i want to do is to efficiently store a maximum of N recent changes that have been made to each warehouse that is stored in the table. I have considered creating a "helper" table (e.g. warehouse_changes) and taking care of the updates through my application, but honestly it feels like there is a smarter way around this.

Is there a way to store a specific amount of entries per warehouse and automatically manage updating the right element through mysql workbench? Thanks in advance and keep in mind that i'm not particularly advanced in this field.

Answer 1

There is a very detailed article on O'Reilly Answers that describes how to do exactly what you want using triggers.

When explained in two words, you need to create a helper table and a trigger per each operation type that you want to store. For example, here's how a trigger for updates looks like according to that article:

-- Creating a trigger that will run after each update
-- for each affected row
CREATE TRIGGER au_warehouse AFTER UPDATE ON Warehouse FOR EACH ROW

BEGIN

  -- Insert new values into a log table, or you can insert old values
  -- using the OLD row reference
  INSERT INTO warehouse_log (action, id, ts, name)
      VALUES('update', NEW.id, NOW(), NEW.name);

END;

After that you can get the latest 1000 changes using a simple SQL query:

SELECT * FROM warehouse_log ORDER BY ts DESC LIMIT 1000;