Philip van Gass
asked on
Complex update statement not working
Good day Experts,
Please view the attached script which is an update trigger. Neither of the two update statements are working. I know that the processing reaches that part of the script because the message in the THROW statement displays, therefore something is wrong with the logic.
I cannot see it and therefore the only other way seems to be a CROSS APPLY or a CURSOR which I am not keen on.
Updating_agent_functions.sql
Please view the attached script which is an update trigger. Neither of the two update statements are working. I know that the processing reaches that part of the script because the message in the THROW statement displays, therefore something is wrong with the logic.
I cannot see it and therefore the only other way seems to be a CROSS APPLY or a CURSOR which I am not keen on.
Updating_agent_functions.sql
My best guess is that this INNER JOIN is not being met:
INNER JOIN inserted I
ON CF.iAgentID = I.iAgentID AND CF.iSystemFunctionID = I.iSystemFunctionID AND CF.cAgentType = 'G'
which of course would cause the UPDATE to not affect any rows.
INNER JOIN inserted I
ON CF.iAgentID = I.iAgentID AND CF.iSystemFunctionID = I.iSystemFunctionID AND CF.cAgentType = 'G'
which of course would cause the UPDATE to not affect any rows.
ASKER
Good day Scott,
I am going to try a CURSOR solution to the problem but would appreciate it if you would work out a possible CROSS APPLY solution as I know that you are good at that.
I am going to try a CURSOR solution to the problem but would appreciate it if you would work out a possible CROSS APPLY solution as I know that you are good at that.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Before update you can write simple query to select * from the same query and see, whether you are getting some records with the inner join based on conditions or not.
It might be the case that there are no relevant records and that is why no Update is happening.