title: Data change history with audit tables: Grouping changes
body: <p>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 <code>active</code> that will be set to <code>0</code> instead of a deletion).</p>
  
  <p>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 <strong>one step</strong> and not as 10 independent steps. <strong>Is there a good solution to group such changes together?</strong> Maybe it is possible to have a counter that is incremented each time the trigger is ... triggered? I have never worked with triggers.</p>
  
  <p>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?</p>
  
  <p>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.</p>