MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger I have Created a trigger, where I am storing some specific data in a table and after the trigger gets executed I am deleting that Table.

The query :  

CREATE TRIGGER `afterinsert_Golden_Ball` AFTER INSERT ON `team_trophies` FOR EACH ROW 

BEGIN

    DECLARE badgeID INT(10);   

   CREATE TABLE tempname AS 
     (SELECT AVG(d.fair_play) as AvgPlay , b.tournament_id , d.* FROM 
     `team_trophies` a INNER JOIN tournament_scheduling b ON
      a.tournament_id = b.tournament_id INNER JOIN matches c ON b.id 
      = .match_id INNER JOIN user_match_result d ON c.id = d.match_id WHERE 
      a.tournament_id = 1 GROUP BY d.user_id);

  IF EXISTS (SELECT * from tempname WHERE AvgPlay = (SELECT MAX(AvgPlay) FROM tempname))    

  THEN

        SELECT badge_id INTO badgeId FROM badge_master WHERE name  = 'Golden Ball';

        Insert into user_badges(badges_id , user_id , match_id , turf_id ,
           tournament_id , created_date)
        SELECT  badgeID , user_id , match_id , NULL , tournament_id , 
           created_date from tempname limit 1;

   END IF;

   DROP TABLE tempname;

END  


MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger  

Kindly Help.
(Too much for a Comment...)  (Working backwards...)

LIMIT 1, but no ORDER BY??  You don't care which item you get?

SELECT .. INTO badgeId, but then don't really use it?  Perhaps the subsequent SELECT should have WHERE name  = 'Golden Ball'?

Instead of doing IF EXISTS, try to cause the final SELECT to return nothing.  That would have the same effect, and will probably eliminate the 'need' for a temp table.

Does that IF EXISTS do anything other then see if there are any rows in tempname??  If so please explain.

Is d.user_id the PRIMARY KEY of team_trophies?  If not, the GROUP BY may be improper.

No mention of OLD. or NEW.??

I suspect the INSERT .. SELECT could be simply use the original SELECT, plus an additional WHERE clause based 'Golden Ball', but I am too lost to attempt to rewrite it.