Friday, September 18, 2009

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:

10 comments:

  1. Pretty sure you can do that in msSQL too. In fact, I KNOW you can, since I've done it before. :D

    ReplyDelete
  2. That day is appearing gradually towards them.So they are very excited for this.Hope that everything goes in their favor.Wishing them good luck.



    Boomboxes

    ReplyDelete
  3. As they have to send different files to their clients at a time so they have to change their folders after every delivery.So for this they have to spend more times for this.



    Camera

    ReplyDelete
  4. I am trying to create a MySQL table with three columns. Two colummns are for data and a third the time at which the data is inserted. When I create the table, how do I write out the command to tell MySQL to create a timestamp column?


    ciudadela

    ReplyDelete
  5. Truly i am rattling aim after publicized the article in this site. It has lot of historic things. I necessary to recognize whatsoever different things from this collection.
    IR35 Accountants

    ReplyDelete
  6. Really its a smashing information.This site has lots of vantage. I institute umteen stimulating things from this place. I equal it real much. Its so interesting.I requisite to intercourse this subject with many of my finis friends. So thanks this post.
    Forex Day Trading

    ReplyDelete
  7. This page is simply enlightening and enjoyable to check out. I discovered a whole lot through discovering this. Thank you for putting up awesome written content and then keep up the excellent posting great stuff.
    Play Backgammon iPad

    ReplyDelete
  8. I admire the valuable information you offer in your articles. I will bookmark your blog and have my children check up here often. I am quite sure they will learn lots of new stuff here than anybody else!
    Gin Rummy For iPad

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete
  10. The MySQL activate is a file dissent that is linked with a table. hire research paper writers It will be authorized when a describe action is executed for the table.The activate can be executed when you run one of the going with MySQL clarifications on the table.

    ReplyDelete