0

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.

0

(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.

0

This is a trigger, which is same as function could be called multiply time at unpredicted for You time

so the code like

CREATE TABLE tempname;

DROP TABLE tempname;

illegal for this type of code.

Just think what would be if 2nd session start insert at the same time? (100% realistic situation in any multi user environment)

For prevent collisions - this is not allowed.

1
  • The reason that it isn't allowed is because a CREATE TABLE does an (implicit) COMMIT. And we can't commit inside a trigger. Any actions that a trigger performs are committed (or rolled back) together with the action that triggered it. – ypercubeᵀᴹ Nov 19 '17 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.