I've got a table (foot_ttd) and wish to sort it climbing down (num) and place rating amounts into rating column.

Initial Table

Something of that nature:

Result Table

I have produced a process.

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;

After I refer to it as - it really works great.

CALL proc_ft_ttd_sort;

Next I have produced trigger calling this process.

CREATE TRIGGER au_ft_ttd_fer AFTER UPDATE ON ft_ttd FOR EACH ROW 
BEGIN
CALL proc_ft_ttd_sort(); 
END;

Now each time after i update foot_ttd table I have 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 steps to make it work? Maybe this method could be enhanced? Thanks!

The create table statement is definitely an implicit commit, becasue it is DDL. Essentially, the reply is you cannot produce a table inside a trigger.

http://dev.mysql.com/doc/refman/5.0/en/stored-program-restrictions.html

Triggers can't get it done

DDL aside, your trigger-based approach includes a couple of difficulties. First, you need to customize the very table which has been up-to-date, and that is not permitted in MySQL 5.

Second, you want an argument-level trigger instead of For Every ROW &mdash you don't need to re-rank the entire table for each affected row &mdash but that is not supported in MySQL 5.

Dynamically compute "rating"

So ... could it be enough to simply compute rating dynamically utilizing 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;

Regrettably, you can't wrap that Choose inside a VIEW (since a view's "SELECT statement cannot refer to system or user variables"). However, you can hide that inside a selectable saved 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 should you must

Like a kluge, should you must store rating within the table instead of compute it, you are able to run this UPDATE when 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 the consumer code to disregard rows where rating IS NULL &mdash individuals weren't rated yet. Better, produce a VIEW that does that for you personally.

Kluging further, you are able to likely regularly UPDATE via CREATE EVENT.