MySQL triggers - automated magic

Working on a tenzui software project, I found a need to register every occuring change in the database, and it was not a hard task to realize that adding code for such monitoring to every single page, including ones generated by the client later on, would be cumbersome indeed. After some code-site traversing, I had my solution worked out. The glory of the open-source MySQL database shines through again - with a simple statement, a trigger, any desired action could easily be caught and acted upon. Diving in:

DELIMITER //
CREATE TRIGGER on_delete_from_archive
AFTER DELETE ON archive
FOR EACH ROW BEGIN
INSERT INTO changelog (action, table_name, item_name, user_id)
VALUES ('DELETE', 'archive', OLD.name, $_SESSION['userID']; END//
DELIMITER ;

A very simple, yet powerful example.

Suppose we have a table named archive, holding information about meeting minutes, publicly available documents etcetera. In your organization, you have a limited number of people who are able to add and remove content to this table. All good, until that day someone sets her UI password to "god," and pandoras box is opened. Now, some pesky kid playing deity quickly vaporizes your collection of .pdfs, leaving you devastated. Or not really, because you've got your backups, of course. But how did it happen?

By checking your changelog table, you can now see who and at what time any post was deleted from your archive table. Pinpointing the error to the haphazardly protected user, it's a small task to fix the hole. All with just a few lines of SQL, acting over all scripts making changes to the specified table. I find it pretty useful.

So, what does TRIGGERS support, really?
A trigger acts on the execution of any INSERT, DELETE or UPDATE statement upon the specified table. The event you want performed can take place either BEFORE the query has been run, or AFTER it. It also has the ability to handle "passing data," that is, the OLD and NEW data (which also happens to be the keywords for said operations).

These statements gives you ample opportunity to perform comparisons on two related pieces of data before performing the query activating the trigger, or as in our example above, log the successful execution of the query, together with related interesting data.

A thing of importance to take notice of is that triggers *only* react to SQL statements. APIs etc. can perform tasks on the tables without "triggering."

As you saw above, a trigger is created by invoking the "CREATE TRIGGER" statement. Up until recently, one would require the SUPER privilege to execute this, but with MySQL version 5.1.6, a new GRANT was added for this purpose: GRANT TRIGGER.

GRANT TRIGGER ON table.database TO 'user'@'localhost'

Thanks for your time! I hope you found these short notes useful, and that you'll make use of the awesome TRIGGER-statement in the future.

For more reading up, I'd recommend the following links:

• MySQL reference manual, Using Triggers
• MySQL reference manual, Trigger Syntax