Jump to content
  • 0

Trigger Help


pmcfarlain
 Share

Question

I need help debugging a trigger. The whole trigger is very big because it's all the actions that need to happen when any data in the table is updated. Trying to avoid too much cascading. But here's what's relevant to my issue:

 813834245_ScreenShot2021-06-29at4_22_45PM.thumb.png.8cd1a461c75d3001c3574d1b5a61853e.png

So I have two tables, one for current notes and one for the history of all notes ever added to a job. In my job table, I have a Notes column and a New Notes column which is perpetually blank so that new notes can be added. What this trigger is supposed to do is insert the old note into my history table along with the Job ID and who submitted it. Then, the update should put the new note into the Notes column and empty out the New Notes column. What actually happens is the second part of the trigger works perfectly and adds the new note to the notes column but something goes awry with the first part and it adds 2 new rows to my history table with the note as completely blank and it also changes any pre-existing records with the same Job ID to be blank ????

I am at a loss. Please help!

Link to comment
Share on other sites

1 answer to this question

Recommended Posts

  • 0
16 minutes ago, pmcfarlain said:

I need help debugging a trigger. The whole trigger is very big because it's all the actions that need to happen when any data in the table is updated. Trying to avoid too much cascading. But here's what's relevant to my issue:

 813834245_ScreenShot2021-06-29at4_22_45PM.thumb.png.8cd1a461c75d3001c3574d1b5a61853e.png

So I have two tables, one for current notes and one for the history of all notes ever added to a job. In my job table, I have a Notes column and a New Notes column which is perpetually blank so that new notes can be added. What this trigger is supposed to do is insert the old note into my history table along with the Job ID and who submitted it. Then, the update should put the new note into the Notes column and empty out the New Notes column. What actually happens is the second part of the trigger works perfectly and adds the new note to the notes column but something goes awry with the first part and it adds 2 new rows to my history table with the note as completely blank and it also changes any pre-existing records with the same Job ID to be blank ????

I am at a loss. Please help!

 

Most of these are not needed.

 

1.) You don't need NEW NOTES field, you only need one, so you won't need the 2nd part anymore

 

2.) INSERT INTO tbl_job_notes SELECT FROM Job Table instead of #inserted

 

This is an example

image.thumb.png.50d5aa392eca36f3af283374835bc64a.png

 

In this, if I update Table one Quantity, what will get inserted in tableTwo is the OLD quantity since the new quantity is in the #inserted.Quantity


You can add Where clause on the Select From if you want to check if the specific field has been updated (you're right with the tableone not equal #inserted)

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.

Guest
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.

Loading...
 Share

×
×
  • Create New...