1

Hello I have three tables in my MySQL-DB

Project:
id | name
1  | project1
2  | project2

KPI:
id | name
1  | build_status
2  | satisfaction

ProjectKPIData:
id | kpi_id | project_id | value
1  | 1      | 1          | 2
2  | 1      | 2          | 0
3  | 2      | 1          | 1
4  | 2      | 2          | 1

Now I am trying to add a trigger to the Project-table. If a new project gets added it should insert as many rows into ProjectKPIData as there are KPI's and also insert the id of them. So after I add a new project the table ProjectKPIData should look like this (the project id 3 is for the newly created project):

ProjectKPIData:
id | kpi_id | project_id | value
1  | 1      | 1          | 2
2  | 1      | 2          | 0
3  | 2      | 1          | 1
4  | 2      | 2          | 1
5  | 1      | 3          | 0
6  | 2      | 3          | 0

Is there a specific MySQL query I can acomplish this? Im pretty new to MySQL-Databases and I couldn't find a way of doing this while searching online.

Thanks for the help.

1

You can use this query.

INSERT INTO ProjectKPIData (`kpi_id`,`project_id`,`value`) SELECT k.id,NEW.id,0 FROM KPI k;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.