Trigger updating all rows

I’m having problems with a MySQL TRIGGER.

I have an employees table with some basic information. Then I have a VIEW that we call our finance_system. The finance_system VIEW holds a lot of information from many sources. A few columns in the finance_system are related to an internal sales draw and return random values from other tables each time the finance_system VIEW is queried. Lastly, I have a table called EP1. Its job is to hold some of the data from the finance_system, particularly some of the columns with random values. This keeps them from UPDATING /Changing with every query. The EP1 table gets updated via a TRIGGER that fires AFTER AN UPDATE to certain columns within the employees table. The trigger works and the EP1 table gets updated, but it’s updating the entire table rather than the rows associated to the employee_id that was updated.

I read Stacks policy prior to posting and understand a reproducible example is necessary so I created a Fiddle, but I’ve obfuscated the data and only included a few columns. Hopefully it’s enough as I’ve exhausted Google trying to figure out why it’s not working.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=942dbabba180df86b7ac9317bbcdd64b

Edit:

Simpler version: When Table1.status is updated, the trigger should update Table3.rand1 with the values from Table2.rand1 where the ID’s match. Each ID has two rows in Table2 and Table3. My current problem is that when the trigger fires it updates all the rows in Table3 and I want it to only update the rows associated to the ID that was update in Table1. A join won’t work in this scenario, as Table2 is actually a VIEW using rand() in my real data.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=329e2dd3bf2fff39afc5388721c53ddc

Aimee

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Check the trigger’s UPDATE query: it is missing a WHERE clause, hence all rows of Table3 get updated instead of the ones matching the desired id.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x