Jump to content
  • 0

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



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?



Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0

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? 
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...