Inserting to another table an existing data from different table

[alexmshcare]

Hi, 

I was trying to have existing data from a table to be inserted into another table if the incident category falls into any of the requirements when the amendment was done but it's not working. 

 

[CoopperBackpack]

Hello @alexmshcare,

I am not sure that I got the Trigger goal. 

From the screenshot, I can see that the 'Staff_SpotCheck' table stores unique records that are identified by the 'SpotcheckID'.

I assume that the 'Staff_IncidentScoreBoard' table is a so-called child table, so it can store multiple records with the same SpotcheckID.

My understanding of the workflow is the following: 

1) The user updates a record in the Staff_SpotCheck' table.

2) The 'IncidentCategory' field of the updated record contains one of the listed values.

3) The 'SpotcheckID' value in the updated record does not exist in the 'SpotcheckID' field of the 'Staff_IncidentScoreBoard' table.

4) If the conditions listed above are met a new record should be inserted into the 'Staff_IncidentScoreBoard' table. Values to insert are taken from the updated record.

If this is true, you may use the LEFT JOIN  with an additional condition:

If this is not correct, please clarify the Trigger goal. 

[kpcollier]

I'm guessing it's something with your JOIN. I'd try removing your join altogether and putting the comparison in your WHERE section. Add an AND block outside of your OR block, and make it like "#inserted.SpotcheckID not exists SELECT SpotcheckID from StaffIncidentScoreBoard_Table". That way it would be your new comparison AND one of the 4 contains in your WHERE block.

[Tubby]

Just to make sure I understand your workflow, when an update is made in the table where this triggered action contains any of the incident category values that you listed in the WHERE condition, it should insert that record in Staff_IncidentScoreBoard table if the record doesn't exist in the table yet? If yes, then I agree with @kpcollier's comment. The JOIN is the problem because it basically adds a condition that it needs to find a record in the other table that is not matching the spotcheckID of the inserted record.

[alexmshcare]

I am not sure if I followed the steps correctly, when I took off JOIN, the field dropdown changed from inserted.STID to just STID. See the encircled part on my new screenshot. 

When I enter an update on existing data from Staff_SpotCheck table, it doesn't add up on the Staff_IncidentScoreBoard Table.
 

[alexmshcare]

I appreciate all the help. @CoopperBackpack, I applied the steps you gave and it worked on both tables I am trying to set up the trigger. 

Thank you.