MySQL Error when trigger calls a procedure

Question

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!

score 2 · Answer 1 · answered Nov 08 '11 at 14:03

2

The create table statement is an implicit commit, since it's DDL. Basically, the answer is you can't create a table in a trigger.

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

answered Nov 08 '11 at 14:03

Jeremiah Gowdy

5,476
3
21
33

How can i make this operation otherwise? – Oleg Legun Nov 08 '11 at 14:21
@oleglegun best I can think of is to make the temporary table not temporary. That would eliminate the DDL from the trigger. – Jeremiah Gowdy Nov 08 '11 at 14:33
@oleglegun the truncate table and drop commands are also going to trigger the issue. You may have to change the design here to something other than a trigger, due to the limitations of MySQL. – Jeremiah Gowdy Nov 08 '11 at 15:15
@olegleun what version of MySQL are you using? You want to at least be on 5.0.17 – Jeremiah Gowdy Nov 08 '11 at 15:18
mySQL 5.5.9. Thats very awkward that mySQL can't call a procedure out of the trigger( – Oleg Legun Nov 08 '11 at 15:28
@oleglegun I agree, but unfortunately that's the answer. – Jeremiah Gowdy Nov 08 '11 at 15:33
1

@oleglegun, just to be clear, MySQL triggers _can_ call procedures, but there are limitations on what such procedures may do in the context of a trigger. – pilcrow Nov 09 '11 at 15:47
@pilcrow Upvoted for clarifying that. I'm just saying *that* stored procedure doesn't work from the trigger because it involves DDL and other disallowed operations. – Jeremiah Gowdy Nov 09 '11 at 19:07

score 1 · Accepted Answer · edited May 23 '17 at 12:17

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.

MySQL Error when trigger calls a procedure

2 Answers2

Triggers can't do it

Dynamically compute "rating"

Or UPDATE if you must

Dynamically compute `"rating"`