Trigger Help

[pmcfarlain]

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:

 

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!

[TellMeWhy]

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:

 

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

 

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)