none
Subquery returned more than 1 value

    Question

  • Hello guys,

     

    I'm having a problem executing a trigger that will fire once an update of multiple rows happens. I get this problem statement:

     

    "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

     

    I'm having a trigger that will send a mail once an update happened to a column. Actually when I update one row (by specifying WHERE clause) the code executes successfully and it send an email.

     

    I used this:

    Code Snippet

    UPDATE [ISC_Dev].[dbo].[ISC_Workshop] SET [Confirmation] = 1 WHERE [Approval] = 1 AND Register_ID = 3

     

    But when I update multiple rows (I don't specify WHERE clause) it give me above error. The result of the query below is more than one row and here comes the issue when trying to update more than one row and at the same time executing the trigger

    I used this to update multiple rows. It is working when the trigger is disabled but when I enable the trigger it gives me an error:

    Code Snippet
    UPDATE
    [ISC_Dev].[dbo].[ISC_Workshop] SET [Confirmation] = 1 WHERE [Approval] = 1

     

    I Googled the error and I found that you can not execute a trigger if you are updating multiple rows. I found this article (Multirow Considerations for DML Triggers) http://msdn.microsoft.com/en-us/library/ms190752.aspx and I actullay could't do it. So if you have an idea of how to implement this method please tell me.

     

    Now, How can I overcome this issue and find a solution? and is there any other way to do it? if yes, please share!

     

    I'm including the T-SQL code:

     

    Code Snippet
    USE [ISC_Dev]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER TRIGGER [dbo].[SendConfirmationMail]
       ON [dbo].[ISC_Workshop]
       AFTER UPDATE
    AS
    BEGIN
     IF UPDATE([Confirmation])
      IF ((SELECT [MailSent] FROM INSERTED) = 'False')
       BEGIN
        SET NOCOUNT ON;
        BEGIN
         -- Declare Variables
         DECLARE @_ProfileName NVARCHAR(100)
         DECLARE @_Recipients NVARCHAR(100)
         DECLARE @_CopyRecipient NVARCHAR(100)
         DECLARE @_Body   NVARCHAR(2000)
         DECLARE @_TraineeName NVARCHAR(100)
         DECLARE @_Subject  NVARCHAR(100)
         DECLARE @_RegisterID INT
         DECLARE @_WorkshopID INT
         --Set Variables     
         SET  @_ProfileName = (SELECT [scr_mail].[ml_profile_name] FROM [scr_mail] WHERE [scr_mail].[ml_id] = 1)
         SET  @_Recipients = (SELECT [E_Mail]      FROM INSERTED)
         SET  @_CopyRecipient = (SELECT [Manager_E_Mail]    FROM INSERTED)
         SET  @_Subject  = (SELECT [scr_mail].[ml_subject]  FROM [scr_mail] WHERE [scr_mail].[ml_id] = 1)
         SET  @_RegisterID = (SELECT [Register_ID]     FROM INSERTED)
         SET  @_WorkshopID = (SELECT [Course_ID]     FROM INSERTED)
         -- Build Mail Body --
         -- Declare Mail Variables 
         DECLARE @MailBody  NVARCHAR(2000)
         DECLARE @Name   NVARCHAR(100)
         DECLARE @WorkshopName NVARCHAR(100)
         DECLARE @WSDate   NVARCHAR(100)
         DECLARE @WorkshopID  INT
         DECLARE @varResult  NVARCHAR(2000)
         -- Set Mail Variables 
         SET @MailBody  = (SELECT [ml_body] FROM scr_mail)
         SET @Name   = (SELECT [Prefix] + ' ' + [First_Name] + ' ' + [Last_Name] FROM INSERTED)
         SET @WorkshopID  = (SELECT [Course_ID] FROM INSERTED)
         SET @WorkshopName = (SELECT DISTINCT [WorkshopName] + ' (' + [WorkshopDesc] + ') ' FROM vw_workshops_details WHERE [WorkshopID] = @WorkshopID)
         SET @WSDate   = (SELECT DISTINCT + '[' + CONVERT(CHAR(10), [WorkshopStartDate], 103) + '] to [' + CONVERT(CHAR(10), [WorkshopEndDate], 103) + ']' FROM vw_workshops_details WHERE [WorkshopID] = @WorkshopID)
         -- Execute StoredProcedure Passing all paramerters
         EXECUTE spPrepareMail @MailBody,@Name,@WorkshopName,@WSDate,@WorkshopID,
         @varResult OUT
         SET @_Body = @varResult 
         -- Update MailSent(T/F) and MailSentDate
         UPDATE [ISC_Workshop]
         SET  [MailSent]  = 'True',
           [MailSentDate] = GETDATE()
         WHERE [Register_ID] = @_RegisterID
         -- Set NONCOUNT to 'ON'
         SET NOCOUNT ON
         -- Send Mail
         EXEC msdb.dbo.sp_send_dbmail
         @profile_name  = @_ProfileName,
         @recipients   = @_Recipients,
         @copy_recipients = @_CopyRecipient,
         @body    = @_Body,
         @subject   = @_Subject;
        END 
       END
      END

     

     

    Thanks in advanced and I hope to find a solution ASAP.

     

    Regads - Jabri, Himyar

     

    Monday, October 27, 2008 8:55 AM

All replies

  •  

    I Googled the error and I found that you can not execute a trigger if you are updating multiple rows

    FALSE

     

    You just have to handle this event appropriately in your T-SQL!

     

    I believe your error will be here here:

    Code Snippet

    (SELECT [MailSent] FROM INSERTED) = 'False'

     

    If you're affecting more than one row in your transaction then the SELECT statement returns more than one value, causing the error you've observed.

     

    Because of what you're trying to do here (i.e. sending emails) then I'm afraid your solution may have to resort to a CURSOR.

    Monday, October 27, 2008 9:05 AM
  •  

    Have a look at these three snippets

     

    Code Snippet

    DECLARE @id int

    select @id =1 --ONE VALUE PASSED

    Print @id

    --NO ERROR--

    --Output = 1

    DECLARE @id int

    SET @id =(SELECT id from sys.sysobjects) --MANY VALUES PASSED

    Print @id

    ----ERROR-----------

    Msg 512, Level 16, State 1, Line 2

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

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

     

    DECLARE @id int,

    SELECT @id = id from sys.sysobjects --MANY VALUES PASSED

    Print @id

    --NO ERROR BUT BUG as ONLY last ID is assigned to the var-----

    --output = 2137058649

     

     

    As you see in you trigger you are settin gmany variables. You can set the variable to only single value and in your second update there are more than one value .

     

    SO you need to write a LOOP to set these variables -->SEND mail -->Reset then vars to next recepient and so on....

     

    Hope you understand where the problem is

     

     

    Monday, October 27, 2008 9:37 AM
  •  gvee wrote:

     

    I Googled the error and I found that you can not execute a trigger if you are updating multiple rows

    FALSE

     

    You just have to handle this event appropriately in your T-SQL!

     

    I believe your error will be here here:

    Code Snippet

    (SELECT [MailSent] FROM INSERTED) = 'False'

     

    If you're affecting more than one row in your transaction then the SELECT statement returns more than one value, causing the error you've observed.

     

    Because of what you're trying to do here (i.e. sending emails) then I'm afraid your solution may have to resort to a CURSOR.

     

    Thank you for your prompt response qvee

     

    What I'm trying to achieve from that IF statment is it should act as a flag, it that MailSent flag is False then it means that the email had not been sent and it will send the email and will change the status of the flag to True (you can check the code again).

     

    The result of the query is more than one row and it should loop through the records and then change the status of Confirmation column to true and then the trigger will be fired and then it will send the mail.

     

    Please revert back in case you need further explaination .. Thanks again

    Monday, October 27, 2008 10:01 AM
  •  

    You can execute trigger even if you are updating multiple rows.

     

    But, when you update multiple rows, there will ofcourse be multiple rows in "Inserted" table. So, the problem is in statement

     

    IF ((SELECT [MailSent] FROM INSERTED) = 'False')

     

    Thus, the best way is to use loop through the rows of "Inserted" table. You can check each row and update them individually.

    Or else you can check the distinct count of mailsent in "Inserted" table and check whether or not all of them are 'False".

    Like this

     

    select count(distinct [MailSent])
    from INSERTED

    and check the count is 1 or not. If it is 2, then some are "True" and some are "False".

    If the count is 1 then check the distinct value is "False" or not like this.

     

    select distinct [MailSent]
    from INSERTED

    This way you can solve your problem

     

    http:techcreeze.blogspot.com

    Monday, October 27, 2008 10:02 AM
  • What is it with people and rephrasing and reposting the answer already given previously?!

     

    Code Snippet

    DECLARE @email_address varchar(128)
          , @name          varchar(40)

     

    DECLARE email_cursor CURSOR FOR
      SELECT email_address
           , name
      FROM   inserted
      WHERE  MailSend = 'False'

     

    OPEN CURSOR email_cursor

     

    FETCH NEXT FROM email_cursor INTO @email_address, @name

     

    WHILE @@Fetch_Status = 0
      BEGIN
        --Insert your email code here

        FETCH NEXT FROM email_cursor INTO @email_address, @name
      END

     

    CLOE email_cursor

    DEALLOCATE email_cursor

     

    Monday, October 27, 2008 10:15 AM
  • Thanks guys for your responses ... I solve the issue using Stored Procedure ... Thanks again.

    Wednesday, October 29, 2008 10:04 AM