CODEWITHSUNDEEP
mysqldatabasetriggers
Blue Minnie
Blue Minnie

Reputation: 231

Mysql trigger multiply copies to another table

I have a trigger which copies the inserted data from my table 1 to table 2. I tried inserting 2 data into table 1 and it copies it to my table 2 but the the problem is it copies 2 times the data. Whenever I insert A, B, C into table 1 the value being copied in my table 2 is A, A, A, B, B, B, C, C, C. Please help.

delimiter //
CREATE TRIGGER `copy_table` AFTER INSERT ON table_2
   insert into table_1 (id, code, name)
   select id, code, name
   from table_2;
END;
//
delimiter ;

Upvotes: 0

Views: 704

Answers (1)

Nick
Nick

Reputation: 147206

Your problem is that you are inserting all the data from table_2 into table_1 each time your trigger executes. You need to only insert the new values, which you can do by referring to the NEW pseudo-table:

delimiter //
CREATE TRIGGER `copy_table` AFTER INSERT ON table_2
FOR EACH ROW
BEGIN
   INSERT INTO table_1 (id, code, name)
   VALUES (NEW.id, NEW.code, NEW.name);
END;
//
delimiter ;

Demo on dbfiddle

Update

If you want the trigger to only insert values if they don't already exist in table_1, you can either

  1. add a UNIQUE index on (id, code, name) to table_1 and use INSERT IGNORE in the trigger; or

  2. Update the trigger to check that the data doesn't exist in the table already:

    CREATE TRIGGER `copy_table` AFTER INSERT ON table_2 FOR EACH ROW BEGIN IF NOT EXISTS (SELECT * FROM table_1 WHERE id = NEW.id AND code = NEW.code AND name = NEW.name) THEN INSERT INTO table_1 (id, code, name) VALUES (NEW.id, NEW.code, NEW.name); END IF; END;

    Demo on dbfiddle

Upvotes: 2

Related Questions