First, let me start off by saying I do not like the use of Triggers. That said, I do not know of any other option at this point. I need a temporary solution to the following problem and came up with the use of a Trigger.
The issue I have is I am calling an API and sending a request that will insert the following information into a mysql table. The API is limited and does not allow one of the columns fields to accept what I need. The data inserted comes in as -2 everytime and I need the data to be set from a related table. I though a Before Insert Trigger based on if the field = -2. The field would then be set with information from the related table based on the matching field. Here is some more info to clarify this:
Table 1 (Data being Inserted)
field 1 = "name"
field 2 = "-2"
field 1 ="name" (matches field 1 on table 1)
field 2 = "75"
When an INSERT is performed, the Trigger will replace the incoming -2 data and use the related info from Table 2, field 2 using field 1 as a match.
I hope this information is enough for someone trying to help with this. I am having trouble figuring if this should be a BEFORE or AFTER Trigger as well as limiting it to working when field2 on Table 1 is = -2.
This is also temporary until the API being used can accept Field 2 as a variable on the original request.
Were it me, I would definitely perform data validation - suppose the application attempts to insert '-1' or 'khlkdjhlashd' - data can get corrupted in transition without detection. Any code you write needs to take the real world into consideration - malware and interference happen. :P