Data change history with audit tables: Grouping changes

Question

Lets say I want to store users and groups in a MySQL database. They have a relation n:m. To keep track of all changes each table has an audit table user_journal, group_journal and user_group_journal. MySQL triggers copy the current record to the journal table on each INSERT or UPDATE (DELETES are not supported, because I would need the information which application user has deleted the record--so there is a flag active that will be set to 0 instead of a deletion).

My question/problem is: Assuming I am adding 10 users into a group at once. When I'm later clicking through the history of that group in the user interface of the application I want to see the adding of those 10 users as one step and not as 10 independent steps. Is there a good solution to group such changes together? Maybe it is possible to have a counter that is incremented each time the trigger is ... triggered? I have never worked with triggers.

The best solution would be to put together all changes made within a transaction. So when the user updates the name of the group and adds 10 users in one step (one form controller call) this would be one step in the history. Maybe it is possible to define a random hash or increment a global counter each time a transaction is started and access this value in the trigger?

I don't want to make the table design more complex than having one journal table for each "real" table. I don't want to add a transaction hash into each database table (meaning the "real" tables, not the audit tables--there it would be okay of course). Also I would like to have a solution in the database--not in the application.

Answer 1

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.