MySQL Error : 1422 Explicit and Implicit Commit is not allowed in SP and Trigger
(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.