Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2005
    Posts
    21

    Unanswered: Error: Subquery returned more than 1 value

    can anyone help,

    i have an update statement that i run from vb code which is:
    Code:
    Update [P_Wrn] SET printed = 'Yes' Where UserName = 'ICAMPBEL'
    this is fine.
    however, i have now added a trigger to the table P_Wrn for auditing purposes which is the following:
    Code:
    ALTER TRIGGER [dbo].[Audit_P_WRN_Update] 
       ON  [dbo].[P_WRN] 
       AFTER UPDATE
    AS 
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    	declare @Version int
    	SELECT @Version = (SELECT Version FROM Deleted)
    	declare @WNumber int
    	SELECT @WNumber = (SELECT W_Number FROM Inserted)
    	if  @Version > 0
    	begin
    		insert into [Audit].[dbo].SIRIS_P_WRN SELECT [W_NUMBER]
          ,[S_Number]
          ,[Patient_ID]
          ,[DictatedBy]
          ,[Ward]
          ,[G2DocumentID]
          ,[DateDictated]
          ,[DateTyped]
          ,[Notes]
          ,[Printed]
          ,[UserName]
          ,[DateCreated]
          ,[CreatedBy]
          ,[LastUpdated]
          ,[LastUpdatedBy]
          ,@Version
           FROM Deleted
    	end
    
    	update P_WRN set Version = Version + 1 Where W_Number = @WNumber
    END
    which in itself, works fine, copies the record into the new table.
    what does not work is the actual update statement. when i run from code it is falling over with error:
    [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    anyone know how to solve this???

    thanks in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Very standard problem - you have written your trigger on the assumption that only one row will be updated. You need to write a set based solution.
    HINT - it involves JOINING to deleted rather than mucking about with variables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've never used it, but blindman is a moderately competent T-SQL coder (note the winky -> )
    sqlblindman private pastebin - collaborative debugging tool
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2005
    Posts
    21
    yes, i think i have...
    anyhoo, i have modified the stored procedure that is making the updates, to only do an update 1 record at a time.
    all is well and working fine.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note that you have corrected the symptom not the problem. Better is to correct the trigger. Triggers are notorious for causing real debugging difficulties. I would recommend you work with us and fix the trigger. If not, at the very least thoroughly document it (in the trigger code, sproc code and project documents) so that future developers are aware.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •