MySql insert trigger after inserting data into another table

Question

i have created a table trans in which i want to store data after a insert into table resultate. When I run my trigger, I get back two much data in trans, it seems that for every each row the trigger store the complete statement into the db. How I have to change my trigger that just at the end of the insert statement into resultate, the trigger store the statment once into table trans?

Here is my trigger...

   CREATE TRIGGER trans_trigger
   AFTER INSERT
   ON resultate FOR EACH ROW
   insert into trans(ID_trans, Spieltag, Heimteam, ID) 
SELECT     
     CASE
        WHEN @Heimteam = Heimteam THEN @ID_before
        ELSE NULL
    END AS ID_Final,
    Spieltag,
    @Heimteam:=Heimteam as Heimteam,
    @ID_before:=IDer as ID
FROM
    resultate
ORDER BY Heimteam, Spieltag;

The trigger is going to insert a row into trans for every row in resultate. That's because it's running a INSERT ... SELECT ... FROM resultate with no WHERE clause. To reference just the row that was just inserted, remove the query FROM resultate and use NEW.heimteam, NEW.spieltag, etc. to reference values of the row that was just inserted. — spencer7593
What we're not seeing is a specification, an explanation/definition of what the trigger should do, helpfully illustrated with example data and sample results. Is there a way to get the trigger to not insert a row for every row in resultate? Yes there is... avoid doing an INSERT ,,, SELECT ... that gets every row in resultate. Maybe limit the number of rows returned by the SELECT. Maybe wrap that query in the trigger inside a another SELECT (use the existing query as an inline view), and maybe add a WHERE clause on the outer query, with NEW.col references that limits the rows returned. — spencer7593

Unknown Unknown · Accepted Answer · 2018-12-19T21:41:33

I changed my query. Now just the insert row will be stored in the trans db.

   Select a11.ID_Final, a11.Spieltag, a11.Heimteam,a11.ID from
(
SELECT     
     CASE
        WHEN @Heimteam = Heimteam THEN @ID_before
        ELSE NULL
    END AS ID_Final,
    Spieltag,
    @Heimteam:=Heimteam as Heimteam,
    @ID_before:=ID as ID
FROM
    resultate
ORDER BY Heimteam, Spieltag 
) as a11
  join
 (
Select ID from resultate 
)as a12 
on a11.ID = a12.ID where a12.ID = new.ID;

MySql insert trigger after inserting data into another table

1 Answers