In the article ‘Versioning MySQL data‘, I showed the basics of
implementing a revisioning system using trigger. As Jens Schauder
already pointed out, often the data of a record is spread across
multiple tables, like an invoice with multiple invoice lines.
Having each invoice line versioned individually isn’t really
useful. Instead we want a new revision of the whole invoice on
each change.
The perfect solution
Ideally a change of one or more parts of the invoice would be
changed, a new revision would be created. There are several
issues in actually creating this those. Detecting the change of
multiple parts of the invoice at once, generating a single
revision, would mean we need to know if the actions are done
within the same transaction. Unfortunately there is a
connection_id(), but no transaction_id() function in …
[Read more]