Trigger that inserts multiple rows in a new table

Question

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.

forpas · Accepted Answer · 2021-12-31 20:07:36Z

1

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

answered Dec 31, 2021 at 20:07

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger that inserts multiple rows in a new table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related