BEFORE or AFTER trigger for maintaining audit log

Question

I've been reading the MySql 5.0 comment stream on the create trigger page and I would like to ask the community if the recommendations are good & whether they still apply to 5.1. What I've noticed playing with triggers today is that it is impossible to update a field in the old table using a AFTER UPDATE.

1. Be careful with BEFORE triggers. Constraints may occur, specifically if you are using InnoDB engine, where an insert will fail, but actions from your BEFORE trigger will succeed.
2. Use BEFORE triggers primarily for constraints or rules, not transactions, tweaking the NEW.* columns should be fine.
3. Stick with AFTER triggers for most other operations, such as inserting into a history table or updating a denormalization.

Answer 1

Yes. AFAIK, MySQL 5.1 did not make any changes to the semantics of how triggers work. MySQL tries to support the ANSI/ISO SQL specification for trigger semantics.

You can imagine there's a sequence of operations that runs as a row is written to the database:

1. Run BEFORE triggers
2. Evaluate constraints, enforce NOT NULL, apply DEFAULT values
3. Write the row to the database
4. Update indexes
5. Run AFTER triggers

Once you've reached the AFTER trigger, it's too late to change values in the row. In some databases, you can set NEW.somecolumn = 1234 but this change is silently discarded as the AFTER trigger finishes. In other databases, it helps you understand your mistake by giving you an error either when you define the trigger or when you run the trigger.

AFTER triggers are best used for extra actions to take as a result of INSERT/UPDATE of the row, such as the audit logging you mentioned. For one thing, MySQL only permits one trigger per action per table, so if you are also using a BEFORE trigger to change values and enforce business rules, now you can at least keep the extra actions in a separate trigger. That makes it easier to update one or the other.

The other consideration is that you should probably do extra actions only after you know the row has been saved successfully. E.g. it wouldn't be right to log a change in a BEFORE trigger, and then have the change abort because of a NOT NULL constraint.

For DELETE actions where you need to remove dependent rows in other tables, you may still have to do that in a BEFORE trigger.