CODEWITHSUNDEEP
sqlmysqldatabaseversion-controltriggers
Petah
Petah

Reputation: 46040

MySQL data version control

Is there any way to setup MySQL to every time a row is changed, then a row to another table/database is created with what the data was originally? (with time stamping)

If so how would I go about doing it?

E.g.

UPDATE `live_db`.`people`
SET    `live_db`.`people`.`name` = 'bob'
WHERE  `id` = 1;

Causes this to happen before the update: 

INSERT INTO `changes_db`.`people`
SELECT *
FROM   `live_db`.`people`
WHERE  `live_db`.`people`.`id` = 1;

And if you did it again it would result in something like this:

`live_db`.`people`
+----+-------+---------------------+
| id | name  | created             |
+----+-------+---------------------+
| 1  | jones | 10:32:20 12/06/2010 |
+----+-------+---------------------+

`changes_db`.`people`
+----+-------+---------------------+
| id | name  | updated             |
+----+-------+---------------------+
| 1  | billy | 12:11:25 13/06/2010 |
| 1  | bob   | 03:01:54 14/06/2010 |
+----+-------+---------------------+

The live DB needs to have a created time stamp on the rows, and the changes DB needs to have a time stamp of when the live DB row was updated. The changes DB will also have no primary keys and foreign key constraints.

I'm using InnoDB and MySQL 5.1.49 but can upgrade if required.

Upvotes: 4

Views: 1283

Answers (4)

geoff384321
geoff384321

Reputation: 11

Sorry to comment on an old post, but I was looking to solve this exact problem! Thought I would share this information.

This outlines a solution perfectly:

http://www.hirmet.com/mysql-versioning-records-of-tables-using-triggers

Upvotes: 0

nos
nos

Reputation: 229108

You can create a trigger:

DELIMITER \\
CREATE TRIGGER logtrigger BEFORE UPDATE ON live_db.people
FOR EACH ROW BEGIN
    INSERT INTO changes_db.people(id,name,updated) VALUES(OLD.id,OLD.name,now());
END;
\\

Upvotes: 2

Petah
Petah

Reputation: 46040

This is how I ended up doing it

DELIMITER |

# Create the log table
CREATE TABLE IF NOT EXISTS `DB_LOG`.`TABLE`
LIKE `DB`.`TABLE`|

# Remove any auto increment
ALTER TABLE `DB_LOG`.`TABLE` CHANGE `DB_LOG`.`TABLE`.`PK` `DB_LOG`.`TABLE`.`PK` INT UNSIGNED NOT NULL|
# Drop the primary keys
ALTER TABLE `DB_LOG`.`TABLE` DROP PRIMARY KEY|

#Create the trigger
DROP TRIGGER IF EXISTS `DB`.`update_TABLE`|
CREATE TRIGGER `DB`.`update_TABLE` BEFORE UPDATE ON `DB`.`TABLE` FOR EACH ROW
BEGIN
    INSERT INTO `DB_LOG`.`TABLE`
    SELECT `DB`.`TABLE`.*
    FROM `DB`.`TABLE`
    WHERE `DB`.`TABLE`.`PK` = NEW.`PK`;
END|

DELIMITER ;

Upvotes: 1

Mitch Wheat
Mitch Wheat

Reputation: 300559

Use a Trigger

MySQL support for triggers started with MySQL version 5.0.2.

Upvotes: 4

Related Questions