Q:

MySQL Error when trigger calls a procedure

I have a table (ft_ttd) and want to sort it descending (num) and insert rating numbers into rating column.
Initial Table http://dl.dropbox.com/u/3922390/2.png
Something like that:
Result Table http://dl.dropbox.com/u/3922390/1.png
I've created a procedure.
CREATE PROCEDURE proc_ft_ttd_sort  
BEGIN   

CREATE TEMPORARY TABLE ft_ttd_sort
(id int (2),  
num int (3),  
rating int (2) AUTO_INCREMENT PRIMARY KEY);    

INSERT INTO ft_ttd_sort (id, num)   SELECT id, num FROM ft_ttd ORDER BY num DESC;    
TRUNCATE TABLE ft_ttd;   
INSERT INTO ft_ttd SELECT * FROM ft_ttd_sort;  
DROP TABLE ft_ttd_sort;   
END;

When I call it - it works great.
CALL proc_ft_ttd_sort;

After that I've created trigger calling this procedure.
CREATE TRIGGER au_ft_ttd_fer AFTER UPDATE ON ft_ttd FOR EACH ROW 
BEGIN
CALL proc_ft_ttd_sort(); 
END;

Now every time when I update ft_ttd table I've got a error.
UPDATE ft_ttd SET num = 9 WHERE id = 3;
ERROR 1422 (HY000): Explicit or implicit commit is not allowed in stored function ortrigger.

Any ideas how to make it work?  Maybe this process can be optimized?
Thank you!

A:

Triggers can't do it
DDL aside, your trigger-based approach has a few difficulties.  First, you want to modify the very table that's been updated, and that's not permitted in MySQL 5.
Second, you really want a statement-level trigger rather than FOR EACH ROW — no need to re-rank the whole table for every affected row — but that's not supported in MySQL 5.
Dynamically compute "rating"
So ... is it enough to just compute rating dynamically using a MySQL ROW_NUMBER() workaround?
-- ALTER TABLE ft_ttd DROP COLUMN rating; -- if you like

    SELECT id,
           num,
           @i := @i + 1 AS rating
      FROM ft_ttd
CROSS JOIN (SELECT @i := 0 AS zero) d
  ORDER BY num DESC;

Unfortunately, you cannot wrap that SELECT in a VIEW (since a view's "SELECT statement cannot refer to system or user variables").  However, you could hide that in a selectable stored procedure:
 CREATE PROCEDURE sp_ranked_ft_ttd () BEGIN
     SELECT id, num, @i := @i + 1 AS rating
       FROM ft_ttd CROSS JOIN (SELECT @i := 0 AS zero) d
   ORDER BY num DESC
 END

Or UPDATE if you must
As a kluge, if you must store rating in the table rather than compute it, you can run this UPDATE as needed:
    UPDATE t
CROSS JOIN (    SELECT id, @i := @i + 1 AS new_rating
                  FROM ft_ttd
            CROSS JOIN (SELECT @i := 0 AS zero) d
              ORDER BY num DESC
           ) ranked
        ON ft_ttd.id = ranked.id SET ft_ttd.rating = ranked.new_rating;

Now instruct your client code to ignore rows where rating IS NULL — those haven't been ranked yet.  Better, create a VIEW that does that for you.
Kluging further, you can likely regularly UPDATE via CREATE EVENT.

