0

Hi I have two tables Experiment and Sample. I want to create a trigger such that if there is a entry in the 'Sample' table it should check if 'Experiment_name' and 'Sample_name' is same as the previous entry and if both matches then it should update that row and if not then it should enter a new row.

For example

EXPERIMENT TABLE
Experiment_id(auto_incremented)  Exp_name
1                                  ABC

SAMPLE TABLE
Sample_id   Experiment_id  Experiment_name    sample_name   Sample_result
1              1                  ABC              abc       100
2              1                  ABC              xyz       100
3              1                  ABC              hjk       300


**New Entry - Experiment name - ABC and sample_name - xyz then Sample_result - 200  **

SAMPLE TABLE
Sample_id   Experiment_id  Experiment_name    sample_name   Sample_result
1              1                  ABC              abc       100
**2            1                  ABC              xyz       200**
3              1                  ABC              hjk       300

If Experiment name - ABC and sample_name - wer did not exist then Sample_result - 200 should be inserted as a new row.

SAMPLE TABLE

Sample_id   Experiment_id  Experiment_name    sample_name   Sample_result
1              1                  ABC              abc       100
2              1                  ABC              xyz       100
3              1                  ABC              hjk       300
**4            1                  ABC            wer       200**

I could simply do this using update but I am using MySQL workbench and I will be impoting data from CSV using the import wizard and that is why I want it to be automated.

I am using mysql 5.7.22. Please someone help me, thanks!!

Rupesh Ghosh
  • 57
  • 6
  • This is not possible with a trigger. This sounds however like you want to [Insert into a MySQL table or update if exists](https://stackoverflow.com/q/4205181) (and in case it is not clear from that question: you require a unique key on `(Experiment_name, Sample_name)`, this is the way to tell MySQL about your "condition") – Solarflare Oct 01 '19 at 20:07
  • Thanks for the link! yeah, if the user knows the unique key then it is possible to use it but in my case the user is just going to push the data from excel workbook so he/she will have no idea about the unique key of the entries. – Rupesh Ghosh Oct 02 '19 at 15:57
  • The user does not need to "have an idea" about the unique key. This is something that you define for the table. The unique key will do the following: if you try to insert a row where the combination `Experiment_name, Sample_name` already exists, it will throw an error (as only one unique row is allowed to exist). `on duplicate key update` will prevent this (duplicate key) error and update the existing row instead of inserting a new row. This sounds like what you want to do, and if not, you may need to elaborate why not, and/or what the constraints/requirements/problems with this are. – Solarflare Oct 02 '19 at 17:06
  • And just to emphasize again: in MySQL, it is NOT possible for a trigger to e.g. change an insert to an update (see e.g. ["Instead of” Triggers in mysql](https://stackoverflow.com/q/25469614), which might be what you think of/heard about, which comes to a similar conclusion), its not even possible to change a different row than the one you are currently inserting/updating/deleting in the same table. – Solarflare Oct 02 '19 at 17:11
  • That is exactly what I wanted to do! Thanks! – Rupesh Ghosh Oct 02 '19 at 20:02

0 Answers0