3

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.

3 Answers 3

4

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.

5
  • In terms of the application alone, I would agree. The only issue is--with a young and growing application--there are inevitably tasks that will be performed by hand on the database until an interface is created. These are somewhat rare, but this only increases my wariness of depending on developers to add , revision = revision + 1 to queries they write.
    – Bryan Agee
    Aug 11, 2011 at 18:16
  • Edited my answer. If it has to be a trigger, INSTEAD OF makes sense. Aug 11, 2011 at 18:26
  • I don't think MySQL supports INSTEAD OF triggers yet...
    – Bryan Agee
    Aug 11, 2011 at 18:36
  • I'd swear the MySQL tag wasn't there when I first looked at this question :) Aug 11, 2011 at 18:38
  • It wasn't... I added it after your suggestion. =)
    – Bryan Agee
    Aug 11, 2011 at 18:38
1

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.

2
  • Isn't revision and field change tracking--by definition--audit trail? We are not discussing any business rules here.
    – Bryan Agee
    Aug 12, 2011 at 16:08
  • The audit trail is there because of business rules around accountability. Without the business rules there is no need for an audit trail. The audit table can contain all the relevant data. A revision number field is often used to enable check for concurrent updates when holding locks for the duration of the transaction. You want them incremented on ALL updates. Name of the creator and last updater (and related timestamp) are often kept without tracking what was changed. Time of last update can be used in place of a revision number field, but may not be as reliable.
    – BillThor
    Aug 13, 2011 at 15:27
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.