Insert and count tags automatically using MySQL 5.0/5.1 Triggers

Sometimes, for applications, you don't want or desire the trouble of having to insert pieces of a title for say a blog, or a quoted phrase, into some database that contains/tracks your tags. Such a table probably contains fields about an auto inserted tag, such as the identifier itself, how many times the identifier is found, and whether it is worth utilizing in a popular tag list.


Now, with the power of MySQL triggers you no longer have to rely on the power of outside languages to harness these solutions. There are still some limitations to remember with MySQL triggers.For one, we can not call other stored procedures through a trigger. However, that is really more an inconvenience in organization than in functionality.


Below I will highlight some of the code to update a tag table from an insert to a table I call phrases.

DELIMITER //

CREATE TRIGGER `db_name`.`ins_phrases_update` AFTER INSERT ON `db_name`.`phrases`
 FOR EACH ROW BEGIN 

    declare breakfound numeric default 0;
    declare phraselength , l_loop, temp int default 1;

    set phraselength = length(new.phrase); 
 
    while l_loop < phraselength do
       
       SELECT LOCATE(' ', new.phrase, l_loop) INTO breakfound ;
       
  IF breakfound > l_loop THEN
   INSERT INTO tags (name) VALUES (LOWER(SUBSTRING(new.phrase, l_loop,breakfound-l_loop))) ON DUPLICATE KEY UPDATE total=total+1;

   set temp := l_loop;
   set l_loop := breakfound + 1;
  
  ELSE
   INSERT INTO tags (name) VALUES (LOWER(SUBSTRING(new.phrase, l_loop,phraselength-l_loop))) ON DUPLICATE KEY UPDATE total=total+1;
   
   set temp := l_loop;
   set l_loop := phraselength;
    
  END IF;

    
    end while;

END
//

A few notes of warning to MySQL developers.

• Only one trigger per table operation. While that may sound limiting, I think that again an originizational inconvenience more than anything.
  
• SET can not be used to increment the variable it is acting on. It seems rather Cobal-esque, but you have to declare a temp variable to hold information on any kind of increment or reassign.
  
• Command line for whatever reason seems easier at the moment to create triggers than phpMyAdmin. Possibly my own setup, but if it's an issue, drop to commandline and try the same thing.
  
• Remember DELIMITER before a trigger write, otherwise your MySQL will think you're ending the trigger at the end of your first SQL statement.
  
• This should make sense, but you can not change a "new.value" on and AFTER INSERT operation. With some languages, like php, you can change almost anything temporarily at least, even if it is a special variable.
  

I have only highlighted the INSERT trigger here. Clearly to make this functional, you need also the DELETE, and maybe the UPDATE triggers. The others you will find are trvial, as with DELETE all you need to do is change every "new.phrase" to "old.phrase" and you are pretty much done. UPDATE will need to combine the DELETE and the INSERT operations.