Trigger for INSERT, INSERTS multiple rows-CodePudding

I am creating a trigger that will insert data into tableB after I manually insert data into tableA.

My current issue is that after inserting multiple rows into tableA, tableB will create more rows than needed.

E.g. Inserting 3 rows of data into tableA will result in the trigger inserting 6 rows into tableB. The issue seems to be the trigger tracking every row in tableA for every insert.

The trigger is as follows.

CREATE TRIGGER trigger1
AFTER INSERT ON tableA 
FOR EACH ROW
    INSERT INTO tableB(cust_id, name)
    SELECT new.cust_id, new.name FROM tableA;

The output both tables, that results from the 3 inserts into tableA are as follows.

tableA

cust_id	name
0001	Frodo
0002	Sam
0003	Merry

tableB

cust_id	name
0001	Frodo
0002	Sam
0002	Sam
0003	Merry
0003	Merry
0003	Merry

I am currently looking for a way to have 1 row from tableA create 1 row into tableB and have similar amount of rows into both the tables.

Would be thankful for any help or advice.

CodePudding user response：

for each row - means for each row inserted so instead of insert..select insert..values is more appropriate

CREATE TRIGGER trigger1 AFTER INSERT ON tableA 
FOR EACH ROW 
INSERT INTO tableB(cust_id, name) values(new.cust_id, new.name);