Trigger that inserts multiple rows in a new table-CodePudding

I'm trying to figure out how can I create a trigger that inserts multiple rows in another table, not just once.

I have 4 tables --> Student, Class, Assignment, ClassAssignment

Student: 
ID INT, CLASSID (FOREIGN KEY, references class(ID))
Class:
ID INT,
Assignment:
ID
ClassAssignment (Is a bridge table) from Class And Assignment.

I want, every time a new row is inserted in 'ClassAssignment', FOR EVERY STUDENT that have this 'ClassID', to insert into another table StudentAssignmentSolution, the studentID and assignmentID (both from this inserted row).

create trigger newClassAssignment after insert on classassignment 
for each row
insert into StudentAssignmentSolution(studentID, assignmentID) 
values((select id from student where classID = new.classID), new.assignmentID);

What I don't know to do, is how to make the trigger to insert multiple rows in another table, not just once.

CodePudding user response：

Use SELECT instead of VALUES in your INSERT INTO ... statement, to select from the table Student all the students that have that specific classID:

INSERT INTO StudentAssignmentSolution(studentID, assignmentID) 
SELECT s.ID, NEW.assignmentID
FROM Student s
WHERE s.classID = NEW.classID