CODEWITHSUNDEEP
mysqlsqltriggers
user1201502
user1201502

Reputation: 5

SQL Trigger on Insert into one table, copy into another multiple times

I have 2 tables setup in my database - fitness_report and result.

fitness_report has the following columns (in order):

report_id, test_period, test_date, student_id

results has the following columns (in order):

test_id, student_id, report_id, score

What I need to happen is when a new row is created on table fitness_report, entries are made to the results table as follows, where student_id and report_id are copied from the new row made on fitness_report:

1, student_id, report_id, null
2, student_id, report_id, null
3, student_id, report_id, null
4, student_id, report_id, null
5, student_id, report_id, null
6, student_id, report_id, null

Could you please suggest the best way to go about doing this.

Cheers

Upvotes: 0

Views: 3717

Answers (1)

Husain Basrawala
Husain Basrawala

Reputation: 1751

You can create a trigger on INSERT event. Check this.

CREATE TRIGGER myTrigger AFTER INSERT ON fitness_report 
  FOR EACH ROW BEGIN
    INSERT INTO results SET student_id = NEW.student_id, report_id=NEW.report_id;  
  END;

Upvotes: 1

Related Questions