asked
Assistance with Code - not getting any results!
I need assistance with my trigger. Concept here is when the user inserts an entry in Opportunity table, the nextno table generates a new number. The new generated number is to be inserted into my field on the GUI called Qnumber. so now when i insert the opportunity - the number is not generating in the NextEXP1 table so therefore its not updating the field called Qnumber. is the code wrong? I am using SQL server 2014. Trigger attached![link text][1] [1]: /storage/temp/4325-trig.txt
Without looking at the code in detail, I can see that there are 4 major problems here: There is a line of code reading **Select @CID = Client_ID from inserted**. This might produce unexpected results in case if more than one row is being inserted into the table at once. Suppose there is a script in the form of insert into AMGR_opportunity_Tbl select some_fields from join_of_some_tables; If the above causes the insert of, say, 100 records, then the trigger will still execute only once, because the insert trigger fires once per insert statement, not once per inserted row. The offending line of code will still happily execute, assigning the value of Client\_ID from logical inserted table to the @CID variable 100 times. The value of the variable will end up being equal to the value of the last inserted row, ***bypassing*** the previous 99. Is this what needs to happen? I am not sure about this. Another potential problem is with the line of code reading If @CID not Like '@%' If the @CID variable begins with **@** character then the entire code contents inside of the IF block (pretty much the entire script of the trigger) will be bypassed and nothing will happen. This might not be a problem but I thought that it is worth mentioning because the check is somewhat unusual. It hints that there is a possibility of the Client|_ID value to begin with this character. The third problem is with the line of code reading SELECT TOP 1 @Detail_Name = [Detail_Name] FROM [dbo].[Comb_Opportunity_Details_Alpha] WHERE [Detail_Value] = @Detail_Value By the time this line of code executes, the variable named @Detail_Value is declared, but its value is never set, so there is no way for this select to ever return a row. Finally, there is a problem with the whole bunch of transactions (supposedly one transaction per inserted row) which are on the same level and the errors are not handled. So if something happens with one of the inserts in the middle then the @@trancount might be not what you want it to be. Hope these observations help. Oleg
The two lines of code: Select @CID = Client_ID from inserted If @CID not Like '@%' Are for default entries - this is standard for our system. it shouldnt bypass - it should insert the number that was generated in the NextNo table then insert it into the other table. Thanks for the help and suggestions but i think its quite hard to understand when the DB structure is unexplained. appreciate the ideas though.
@JR03 I never said it will bypass, I said that it will bypass if there is an ID beginning with @. The third problem is real though. I can bet anything that your code will never branch to the part which is actually supposed to be doing the work, simply because you have a variable named ***@Detail_Value*** which is not assigned yet but is already used in the WHERE clause of the select statement (attempting to assign the value of the variable named ***@Detail_Name*** ). This variable is guaranteed to NEVER have any value assigned (except its initial NULL it "receives" during declaration). The only way for the assignment to work is to first set the value of ***@Detail_Value***. Otherwise, you select essentially translates to this: SELECT TOP 1 @Detail_Name = [Detail_Name] FROM [dbo].[Comb_Opportunity_Details_Alpha] WHERE [Detail_Value] = NULL The above needs to be taken care of, i.e. the ***@Detail_Value*** value must be set before using it in the WHERE clause.
18 People are following this question.
