I've got a table (foot_ttd) and wish to sort it climbing down (num) and place rating amounts into rating column.
Something of that nature:
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.
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.
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.
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.