I have to say I am a rather big fan of db triggers, in an effort to bring some discipline to my databases. Since audit tables have become quite a habit of mine, this is probably my main use for triggers. Although MySQL has a little way to go yet before we have the kind of experience that SQL Server has brought to us in the past, but it’s getting there.

I was surprised to see how little documentation there is on the ‘BEFORE’ and ‘AFTER’ keywords. I was looking for something that would explain to me when I should use each of these, and wherever I found *something* on it, it wasn’t enough.

Through a little bit of testing however, I have found that using ‘AFTER’ delivers desirable results for Inserts, Updates and Deletes. Is it important? Well, considering that after performing the tasks of a ‘BEFORE’ trigger the actual Insert, Update or Delete might fail, but this doesn’t undo the effects of the trigger. Using ‘AFTER’ triggers ensures that the task has completed before we create the audit record.

An example of auditing triggers, for Insert, Update and Delete:

CREATE TRIGGER mytable_audit_trigger_insert
AFTER INSERT ON mytable
FOR EACH ROW BEGIN
    INSERT INTO mytable_audit (
        id, date, name
    )
    VALUES(
        NEW.id, NOW(), NEW.name
    );
END;

CREATE TRIGGER mytable_audit_trigger_update
AFTER UPDATE ON mytable
FOR EACH ROW BEGIN
    INSERT INTO mytable_audit (
        id, date, name
    )
    VALUES(
        NEW.id, NOW(), NEW.name
    );
END;

CREATE TRIGGER mytable_audit_trigger_delete
AFTER DELETE ON mytable
FOR EACH ROW BEGIN
    INSERT INTO mytable_audit (
        id, date, name
    )
    VALUES(
        OLD.id, NOW(), OLD.name
    );
END;

It would be nice to be able to query/edit triggers, but unfortunately you can only CREATE and DROP them, certainly within the MySQL Query Browser. I’ve not tried any 3rd party tools as I’m happy to keep .sql files, with the details of the CREATE TRIGGER statements so I know what’s in them.