Development

At any time during database driven development you ask yourself how to keep track of the changes made to your data in your database. I asked myself the same question. And I came up with a simple solution using triggers.

A trigger is a small function stored inside your database that gets triggered on a event in your database. Like, the moment you add or update a row in your table.

For versioning we need 2 things:

- a few extra fields in your table
- a copy of the table structure of the table you want versioning turned on

First, let’s define 2 extra fields:

- enuVersionStatus (ENUM) = ‘INSERT’, ‘UPDATE’, ‘DELETE’
- tsVersionStatus (timestamp) = current_timestamp & on update current_timestamp

enuVersionStatus is defined as a enumerable field that holds the state of the data
tsVersionStatus is defined as a timestamp and holds the timestamp when the data has been edited

Now we copy the entire structure of our table to a log, or history table. Let’s just append a “_log” to this table and remove any indexes or auto_increment statements. And lastly we create a new trigger:

DELIMITER $$
CREATE TRIGGER myTable_Trigger
BEFORE UPDATE ON myTable
FOR EACH ROW
BEGIN
INSERT INTO myTable_log (SELECT * FROM myTable WHERE id = OLD.id);
END$$

What the trigger does is very simple. Just before doing any update on the table, the “old” entry is being written into the log table. The data in myTable holds the live data and the myTable_log holds all the old entries.