Hi Paul, You could try something like this:
Select @RoleType = Min(CallTreeContactRoleId) From CallTreeContacts CTC Join Inserted I On CTC.Uid = I.UID A few points I have to make: - You may get multiple role types - without knowing more about your trigger, I cannot help you. - If you can eliminate the use of a variable, and do an insert/select, for multiple role types, that may be better. - Experiment with Max and Distinct instead of the Min function, and think about the semantic differences. - In statements are expensive, using a join will improve performance, hence reducing the time for the insert statement to complete. If you can provide the source for your trigger, I might be able to help you more. Richard -----Original Message----- From: Discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Paul Cowan Sent: February 12, 2008 10:43 AM To: ADVANCED-DOTNET@DISCUSS.DEVELOP.COM Subject: [ADVANCED-DOTNET] Trigger and multiple values in the inserted table Hi all, To cut a long strory short I have a trigger where I want to update some totals on another table. THe thing is I have the following line: SET @roleType = (SELECT cc.calltreecontactroleid FROM CallTreeContacts cc WHERE cc.[Uid] IN (SELECT [Uid] FROM inserted)) This works if I update one row but if I update multiple rows I get the following error because I have more than one recored in the Inserted table. Subquery returned more than 1 value This is not really suprising.Is there a way for me to do this one at a time without having to resort to while loops or temorary tables? Cheers [EMAIL PROTECTED] _________________________________________________________________ Who's friends with who and co-starred in what? http://www.searchgamesbox.com/celebrityseparation.shtml =================================== This list is hosted by DevelopMentorR http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor® http://www.develop.com View archives and manage your subscription(s) at http://discuss.develop.com
