Having Problem With an Add/Update/Delete Trigger. It's simple, but it's not.

[Lynda]

If I could see the SQL I could probably figure this out, but... This is my first Trigger. Not a good start.

The Situation:

• I have a Log table (WeightLog). When an entry is either Added, Updated, or Deleted, I need to update MyProfile table with two fields (Weight and LastWeightUpdate)
• Because I don't know where the user added, updated or deleted the record, I need to sort the records and take the top 1 record and assume that is the most current record.

What I got Validated (It doesn't mean they work, it just means they validated):   

• The SET and the LEFT join

The error I am getting is "2098: Only one value is expected." Which is kinda what I want. Only 1 record returned in order to update.

Any ideas on what is wrong with this?

Lynda

[CoopperBackpack]

Hello @Lynda,

The error occurs since 3 fields are compared with 1 field.

TOP 1 selects 1 record but this record includes 3 fields. So, this comparison is incorrect. 

I recommend to divide the logic into 3 Triggered Actions: 1 Trigger per each action.

To suggest it would be helpful to know more details about the workflow.

Could you confirm that the points below are correct?

• There is always a record with the specific ProfileID in the MyProfile table first. And then  the user can add a record under the same ProfileID in the WeightLog table.

              So, a scenario when the user adds a record to WeightLog table and there is no record with the corresponding ProfileID in the MyProfile table is impossible

• The WeightLog table can store multiple records related to each ProfileID. 
  If so, how to select the correct record? 
  According to your description it looks like the user can edit the existing record or add a new one. 
  Are you interested in the record with the latest Timestamp where the values of Weight and WeightDate were changed?