What are the best practices for triggers to maintain a revision number on records?

Question

I have an audit trail implemented on an application database that captures old value, new value, modification time, and user inserting it into another table. I'm now wanting to add a revision number to each table to make idempondency checks more efficient.

As it stands, the trigger that captures all the data is AFTER UPDATE. My understanding is that if I want to update the row I'm operating on, I will need to use a BEFORE trigger. ie:

CREATE TRIGGER `Table_A_Audit_Trigger` BEFORE UPDATE ON `Table_A`;
FOR EACH ROW BEGIN;
SET NEW.revision = OLD.revision + 1;

Would it be better to have two separate triggers for this, or to move the insert into the BEFORE trigger as well?

EDIT: The code for the AFTER UPDATE trigger (as well as the table DDL itself) is created by the application layer, then run on the database (generally when the model is created or updated in the software). It would not be difficult to add creation of this trigger at the same time.

As I understand it, the downside to combining into a BEFORE trigger would be inconsistency if the audit insert succeeds, but the actual update fails. Putting everything in an AFTER trigger would require the use of an additional UPDATE on the target table, which further compromises performance, and can cause cascading issues as well.

Answer 1

Along the lines of my response to your earlier question, surely this would be better dealt with in the stored procedure or insert/update query that is responsible for the change. That would be the logical place to increment a version column, not a trigger.

If a trigger is the only way you can be sure the revision count will be implemented, I'd suggest an INSTEAD OF trigger on any tables you need this. You can deal with the revision increment and auditing in one place then.

Answer 2

I find triggers are a good way to enforce things like creating before audit records and version tracking. Audit queries can get a bit bulky, so doing them in a stored procedure may be appropriate. I would still call it from a trigger.

Contrary to what others have said, what you are implementing appear to be business rules, and triggers are a good mechanism for enforcing them. Using a trigger makes it very easy to enforce the rule.

Field by field audits may be better done in a stored procedure, but I would call the procedure from a trigger. Depending on how often the data is consulted, just capturing a before image may be sufficient. In applications using pooled connections, determining who the user is can be difficult.

Which ever way you go this is a good candidate to be automated. The appropriate code can be generated from the tables metadata.

For audits I use an after trigger prefixed AUD (After Update or Delete) to capture the OLD data. I use a standard transformation of the table name for the audit table, although it is possible to use an audit schema to hold the data with a matching table name. Capturing the record image is simpler in the after trigger and helps maintain cohesion within the trigger.

For versioning data I use BIU (Before Insert or UPDATE triggers. You may want to use an if statement to select the appropriate action, or split the trigger into two pieces. Alternatively, you could split the trigger in two, BI and BU. The updates need to happen in a before trigger.

Using standard naming allows you to find missing triggers and audit tables are in place by scanning the metadata. You could use a stored procedure to generate the required components. Bonus marks if it updates the components when the tables definition changes.

Answer 3

After all the discussion and edits regarding this topic, I really can't see how you can do anything other than stick the logic in the AFTER trigger. (To the point where I deleted my previous answer due to this.)

Your business requirements require you to maintain every edit to the table (per comments on my previous answer). Therefore, it has to be done inside of a trigger. If you do it inside a stored procedure, you won't be able to guarantee that all changes will be audited, since they'll be able to access the table directly, bypassing the logic.

Also, due to potential inconsistencies with the BEFORE trigger inserting data but the update failing (per your edit above), you are forced to do the insert into the audit log in the AFTER trigger.

It would be possible to move the logic to a stored procedure and create a transaction (presuming InnoDB) so that if the update fails, the inserts will not occur. However, this still violates your requirement that absolutely every change be audited.

So, given these constraints, there's really no simple way to implement this design other than what you've mentioned (adding the logic to the AFTER trigger).