Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    4

    Unanswered: Subquery returned more than 1 value

    Using SQL Server 2008 Express. Making a trigger to pull the employee# based on the EmployeeName.

    I can't figure out why I'm getting the message that the subquery returned more than 1 value. Does it have something to do with nulls?

    Here's my trigger:


    USE [Personnel]
    GO
    /****** Object: Trigger [dbo].[GetEmpNo] Script Date: 02/08/2010 12:44:05 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author: <Author,,Name>
    -- Create date: <Create Date,,>
    -- Description: <Description,,>
    -- =============================================
    ALTER TRIGGER [dbo].[GetEmpNo]
    ON [dbo].[Disciplinary_Action_Form]
    AFTER INSERT
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @Name VARCHAR(50)
    DECLARE @EEno VARCHAR(5)
    DECLARE @FULL VARCHAR(50)

    SELECT @Name = (Select Name from Inserted)
    Select @Full = (Select "Full" from dbo.Employees$)
    SELECT @EEno = (Select EmpNo from dbo.Employees$ where "Full" = @Name)

    UPDATE dbo.Disciplinary_Action_Form
    SET Name=@Name, EEno=@EEno
    Where F_DocumentID = (select F_DocumentID from inserted)

    END

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    So, how many records do you expect this to return:

    Select "Full" from dbo.Employees$
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    4
    Yep, that was it. I don't know why I had that in there. Always nice to have an extra set of eyes. Thanks!

  4. #4
    Join Date
    Feb 2007
    Posts
    38
    Hi

    Apart from the issue you have for multiple returned FULL, your trigger will also be in trouble if multiple rows inserted at the same time like below:

    INSERT INTO [dbo].[Disciplinary_Action_Form]
    SELECT top 100 *
    FROM [dbo].[Other_Disciplinary_Action_Form]

    Your trigger will only take care of the last row entered out of 100 rows.

    You need to loop through all the rows in the INSERTED table as below:

    DECLARE Disciplinary_Action_Form_Cursor CURSOR FOR
    SELECT Name
    FROM Inserted

    OPEN Disciplinary_Action_Form_Cursor
    FETCH NEXT FROM Disciplinary_Action_Form_Cursor INTO @Name

    -- Loop through cursor until finished
    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- place your code here for after insert action


    --Fetch_Next:
    FETCH NEXT FROM Disciplinary_Action_Form_Cursor INTO @Name

    END

    This routine also should be applied to UPDATE and DELETE triggers as well.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Rather than the cursor (which is usually suspect for performance problems), you should probably join to the inserted table in this case. Instead of
    Code:
    UPDATE dbo.Disciplinary_Action_Form
    SET Name=@Name, EEno=@EEno
    Where F_DocumentID = (select F_DocumentID from inserted)
    You might have
    Code:
    UPDATE dbo.Disciplinary_Action_Form daf
    SET Name=i.Name, EEno=@EEno
    from inserted i 
    Where daf.F_DocumentID = i.F_DocumentID
    You will of course need to work EEno in there, but this should get you started.

Posting Permissions

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