I played a bit around and now I found a very good solution:
The Database setup
# First of all I create the database and the basic table:
DROP DATABASE `mytest`;
CREATE DATABASE `mytest`;
USE `mytest`;
CREATE TABLE `test` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`something` VARCHAR(255) NOT NULL
);
# Then I add an audit table to the database:
CREATE TABLE `audit_trail_test` (
`_id` INT PRIMARY KEY AUTO_INCREMENT,
`_revision_id` VARCHAR(255) NOT NULL,
`id` INT NOT NULL,
`something` VARCHAR(255) NOT NULL
);
# I added a field _revision_id to it. This is
# the ID that groups together all changes a
# user made within a request of that web
# application (written in PHP). So we need a
# third table to store the time and the user
# that made the changes of that revision:
CREATE TABLE `audit_trail_revisions` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`user_id` INT NOT NULL,
`time` DATETIME NOT NULL
);
# Now we need a procedure that creates a
# record in the revisions table each time an
# insert or update trigger will be called.
DELIMITER $$
CREATE PROCEDURE create_revision_record()
BEGIN
IF @revision_id IS NULL THEN
INSERT INTO `audit_trail_revisions`
(user_id, `time`)
VALUES
(@user_id, @time);
SET @revision_id = LAST_INSERT_ID();
END IF;
END;
# It checks if a user defined variable
# @revision_id is set and if not it creates
# the row and stores the generated ID (auto
# increment) into that variable.
#
# Next I wrote the two triggers:
CREATE TRIGGER `test_insert` AFTER INSERT ON `test`
FOR EACH ROW BEGIN
CALL create_revision_record();
INSERT INTO `audit_trail_test`
(
id,
something,
_revision_id
)
VALUES
(
NEW.id,
NEW.something,
@revision_id
);
END;
$$
CREATE TRIGGER `test_update` AFTER UPDATE ON `test`
FOR EACH ROW BEGIN
CALL create_revision_record();
INSERT INTO `audit_trail_test`
(
id,
something,
_revision_id
)
VALUES
(
NEW.id,
NEW.something,
@revision_id
);
END;
$$
The application code (PHP)
$iUserId = 42;
$Database = new \mysqli('localhost', 'root', 'root', 'mytest');
if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()'))
die($Database->error);
if (!$Database->query('INSERT INTO `test` VALUES (NULL, "foo")'))
die($Database->error);
if (!$Database->query('UPDATE `test` SET `something` = "bar"'))
die($Database->error);
// To simulate a second request we close the connection,
// sleep 2 seconds and create a second connection.
$Database->close();
sleep(2);
$Database = new \mysqli('localhost', 'root', 'root', 'mytest');
if (!$Database->query('SET @user_id = ' . $iUserId . ', @time = NOW()'))
die($Database->error);
if (!$Database->query('UPDATE `test` SET `something` = "baz"'))
die($Database->error);
And … the result
mysql> select * from test;
+----+-----------+
| id | something |
+----+-----------+
| 1 | baz |
+----+-----------+
1 row in set (0.00 sec)
mysql> select * from audit_trail_test;
+-----+--------------+----+-----------+
| _id | _revision_id | id | something |
+-----+--------------+----+-----------+
| 1 | 1 | 1 | foo |
| 2 | 1 | 1 | bar |
| 3 | 2 | 1 | baz |
+-----+--------------+----+-----------+
3 rows in set (0.00 sec)
mysql> select * from audit_trail_revisions;
+----+---------+---------------------+
| id | user_id | time |
+----+---------+---------------------+
| 1 | 42 | 2013-02-03 17:13:20 |
| 2 | 42 | 2013-02-03 17:13:22 |
+----+---------+---------------------+
2 rows in set (0.00 sec)
Please let me know if there is a point I missed. I will have to add an action column to the audit tables to be able to record deletions.
