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

Question

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

Husain Basrawala · Accepted Answer · 2012-02-29 08:25:16Z

1

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;

answered Feb 29, 2012 at 8:25

Husain Basrawala

1,75115 silver badges22 bronze badges

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

5 Comments

user1201502 Over a year ago

That'll insert the student_id and report_id into the results table, but how do i insert the static data as well? (test_id and score)?

user1201502 Over a year ago

Would this work: CREATE TRIGGER newReport AFTER INSERT ON fitness_report FOR EACH ROW BEGIN INSERT INTO results(1, NEW.student_id, NEW.report_id, null); INSERT INTO results(2, NEW.student_id, NEW.report_id, null); INSERT INTO results(3, NEW.student_id, NEW.report_id, null); INSERT INTO results(4, NEW.student_id, NEW.report_id, null); INSERT INTO results(5, NEW.student_id, NEW.report_id, null); END;

Husain Basrawala Over a year ago

Try INSERT INTO results SET student_id = NEW.student_id, report_id=NEW.report_id, score=null;. You can repeat the INSERT statement to insert multiple records.

user1201502 Over a year ago

I'm getting the following error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Husain Basrawala Over a year ago

Let me know if it works. I'll update the answer acordingly. Although AFTER INSERT is preferred. You can check for syntax in documentation.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related