Trouble with Update Query 2

[Glowworm27]

Hey all,

I am having trouble with an update query. I keep getting an error that the subquery returned more than one value....

I have a claims table(tblClaims), and each claim has a location(tblClaimLocations), and each location has a District Manager(DMTbl). Now in the Claims table we have the "Current District Manager listed" this is based on who is the manager for the location that is in the claim.

There is a one to one match for claims and locations, a one to one match between locations and District managers.

Now I have a page that allows admins to change the district manager in the location, when they are done I set an UPDATED bit field flag in the locations table so that we know which location was changed.

After the admin is done editing locations I will allow them to click a button that will go thru all the claims and change the current DM to the new DM listed in the location.

trouble is I keep getting an error that the subquery returned more than one row... and its frustrating. here is the update query

Update tblClaims
	Set txtMisc7 = dmtbl.[desc] -- text field of the current DM
	from tblClaims C
	Inner Join tblClaimLocations L On C.lngLocationID = L.lngLocationID
	Inner Join dmtbl on l.txtDistrictMgr = dmtbl.DMID
	where l.updated = 1 --location that was changed
	and NOT (l.txtDistrictMGR  is NUll) --thought this would help with the error, its not helping
	and (dmtbl.[desc] <> C.txtmisc7) -- only change the current dm if the two fields are different

just one last thing there is a trigger on the claim table that will write a record to the notes field and list the fields that were changed, old value and new value.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[vongrunt]

This update query does not have subqueries. Therefore it is not DIRECTLY responsible for error you get. Give us trigger code.

------
chemistry - the only natural science that can be broken down into the categories a) making drugs and b) blowing stuff up

 

[SQLSister]

Since there is no subquery in your code, I suspect it is the trigger which is wrong. It may be set up for single record processing and this query will update multiple records. POst the code for the trigger if you don't immediately see what the issue is.

"NOTHING is more important in a database than integrity." ESquared

 

[Glowworm27]

I was afraid it might be the trigger, here is the trigger.....
The trigger creates a recrord in the notes table when stuff is changed.....

ALTER TRIGGER trgUpdateClaimStatusAndCurrentDMandRM ON dbo.tblClaims 
FOR UPDATE
AS
Declare @oldClaimStatus	nVarchar(50),
		@newClaimStatus	nVarchar(50),
		@OldClaimStatusDate	DateTime,
		@NewClaimStatusDate	DateTime,
		@lngClaimID		Int,
		@txtNote		nvarchar(500),
		@OldDM		nvarchar(70),
		@OldVP		nvarchar(70),
		@NewDM		nvarchar(70),
		@NewVP		nVarchar(70)

--Get the claim ID		
Set @lngClaimID = (Select lngClaimID from Deleted)

Set @OldClaimStatus = (Select txtClaimStatus from Deleted)
Set @OldClaimStatusDate = (Select dteClaimStatus from Deleted)
Set @NewClaimStatus = (Select txtClaimStatus from Inserted)
Set @NewClaimStatusDate = (Select dteClaimStatus From Inserted)

Set @OldDM = (Select txtMisc7 from Deleted)
Set @NewDm = (Select txtMisc7 from Inserted)

Set @OldVP = (Select txtMisc8 from Deleted)
Set @NewVP = (Select txtMisc8 from Inserted)


	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	--Ok the Claim Status text changed lets store the change in the Notes table
	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	IF Len(@oldClaimStatus) > 1	-- Check if there was data in the oldclaimstatus before we right to the notes.
	Begin
		if @oldClaimStatus <> @NewClaimStatus		--If the claim status has changed then we create a Claim Note showing the change.
		Begin
			Set @txtNote = 'Claim Status was changed from ' + @oldClaimStatus + ' to ' + @newClaimStatus
		End
				
		
	End

	-- Write the note to the Claim notes table.
	if Len(@txtNote) > 1 
	Begin
		Insert Into tblClaimNotes(lngClaimID, txtNote, txtUpdatedby)
		Values( @lngClaimID, dbo.EncryptStringnoPwd(@txtNote), 'Updated by trigger')
	End
	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	--OK the Current DM and VP may have changed. lets store the change in the claim notes
	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	Set @txtNote = ''
	
	-- Check if the District Manager has changed
	IF @OldDM <> @NewDm
	Begin
		--The District Manager has changed write the change to the txtNote field
		Set @txtNote = 'District Manager was changed from ' + @OldDM + ' to ' + @NewDm
	End

	-- Check if the Region Mangager has changed
	if @OldVP <> @NewVP
	Begin
		if len(@txtNote) > 1  	-- Check to see if the txtNote has data in it already
		Begin
			--The @txtNote has data in it, append the new text
			Set @txtNote = @txtNote + ' and Region Manager was changed from ' + @OldVP + ' to ' + @NewVP
		End
		Else
		Begin
			-- The @txtNote does not have any data create the new text
			Set @txtNote = 'Region Manager was changed from  ' + @OldVP + ' to ' + @NewVP
		End
	End 

	-- Ok now if there is text in the @txtNote field lets write it to the database.
	if Len(@txtNote) > 1
	Begin
		Insert Into tblClaimNotes(lngClaimID, txtNote, txtUpdatedby)
		Values( @lngClaimID, dbo.EncryptStringnoPwd(@txtNote), 'Updated by trigger')
	End
	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[ca8msm]

Set @lngClaimID = (Select lngClaimID from Deleted)

What happens if the Deleted table has more than one record in it...

This problem is repeated throughout the trigger so as was pointed out above, you should move a set based approach that doesn't assume only one record will be updated.


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[SQLSister]

Never use a values clause in an insert in a trigger, use a select clause instead. That is a key sign that the trigger is designed to handle only one record. Under no cirmustances should a trigger ever be designed to only handle single record inserts,updates or deletes.

And do not replace this with a cursor, you do not want to create a hige efficientcy problem on your server. YOu must replace with a set-based solution.

And after you fix this, I suggest a review of all triggers in your database since they clearly were designed by someone who did not know the basics of trigger design.

Also I would look to your current design in general. Adding information to the notes is a bad practice as well. It is better to have a notes table that is in a one-to-many relationship withthe original table. That way all you have to do is insert the note. You can also have a date field and and user field to see who inserted the note and when. It makes the notes much more useful. Plus they can be displayed so that the latest notes display first as they are likely to be the ones that the user is most interested in.

"NOTHING is more important in a database than integrity." ESquared

 

[Glowworm27]

OK I will look at these items. Lucky for me this is the only trigger in the database. so once I fix this it should be all better.

Thanks for the help.

Peace
G

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[Glowworm27]

OK, I think I got it figured out now, tell me if this trigger will work with multiple records.....

Thanks all for the help!

ALTER TRIGGER trgUpdateClaimStatusAndCurrentDMandRM ON dbo.tblClaims 
FOR UPDATE
AS
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--Ok if the Claim Status text changed write records into the notes table
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	Insert Into tblClaimNotes(lngClaimID, txtNote, txtUpdatedby)
	(Select I.lngClaimID, 'Claim Status was changed from ' + d.txtClaimStatus + ' to ' + i.txtClaimStatus, 'Updated by trigger'
	 From Deleted D
	 Inner Join Inserted I on D.lngClaimID = I.lngClaimID
	 Where d.txtClaimStatus <> i.txtClaimStatus)
	
	
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--OK the Current DM may have changed. lets store the change in the claim notes
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	Insert Into tblClaimNotes(lngClaimID, txtNote, txtUpdatedby)
	(Select I.lngClaimID, 'District Manager was changed from ' + D.txtMisc7 + ' to ' + I.txtMisc7, 'Updated by trigger'
	 From Deleted D
	 Inner Join Inserted I on D.lngClaimID = I.lngClaimID
	 Where d.txtMisc7 <> i.txtMisc7)

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--OK the Current VP may have changed. lets store the change in the claim notes
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Insert Into tblClaimNotes(lngClaimID, txtNote, txtUpdatedby)
	(Select I.lngClaimID, 'Region Manager was changed from ' + Vd.[Desc] + ' to ' + Vi.[Desc], 'Updated by trigger'
	 From Deleted D
	 Inner Join Inserted I on D.lngClaimID = I.lngClaimID
	 Inner Join VPTbl Vd on D.txtMisc8 = Vd.VPID
	 Inner Join VPTbl Vi on I.txtMisc8 = Vi.VPID
	 Where d.txtMisc8 <> i.txtMisc8)

	---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SQLSister]

you don;t need the parentheses around the select statements. But other than that it should work with multiple records. I would test it though with several multiple record updates as well as some single record updates to make sure it is working properly as fara as your business rules are concerned.

"NOTHING is more important in a database than integrity." ESquared

 

[Glowworm27]

Thanks all for the help. I have it working fine now!

oh and SQL Sister in your quote "And after you fix this, I suggest a review of all triggers in your database since they clearly were designed by someone who did not know the basics of trigger design."

I was the person who designed the trigger about 2 years ago, seems I have learned a bit from them, but still have a lot to learn still. Thanks for the help

gave u and ca8msm a star, gave it to ca8msm because it was his reply that clued me to the problem with the trigger right away. And you for suggesting a way to fix the trigger.
both of you were awesome in this respect!

Peace
G

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[SQLSister]

Yes you have learned because I suspect that two years ago, this would not have been so easy for you to fix even when the problem was pointed out. Notice we didn't have to write the code fix for you.

This is one of the reasons why triggers are so tricky. They can create issues years after they are written if badly designed and nobody suspects they are the problem because nobody has worked on that trigger in so long. At least your trigger failed with the multiple record insert. I've seen some that happily take the value from the last record and only that record's information moves to the other table creating a data integrity nightmare that may not even be noticed until months later when someone runs a report and says, "hey why isn't such and such on this report." Be grateful yours failed!

"NOTHING is more important in a database than integrity." ESquared

 

[Glowworm27]

SQLSister,

Yeah I pride myself in trying to learn something new everyday.
I had an inkling that the trigger was causing the problem, and had I given it more than a cursory looksee, I probably would have found the problem. Having spent almost a day trying work out this issue, I decided to give up and give you guys a whack at it. I figured a new pair of eyes would be able to identify the troubles right away. And then hopefully it would be something easy for me to fix, which it was not a problem, once I knew why it was wrong, thanks again both of you for pointing it out for me

Peace



George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net