Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Unanswered: message: Subquery returned more than 1 value. ...

    when I run the query
    UPDATE dbhal.dbo.tblUser
    SET vchrFirstName = FirstName,
    vchrLastName = LastName,
    vchrLogin = LoginName,
    bitActive = Active,
    vchrEmailAddress = EmailAddress
    FROM TempEmployee, tbluser where EmployeeID = intEmp
    and vchrcompid = 'sam'

    and there is trigger

    CREATE TRIGGER UPDATE_tblUser
    ON tblUser
    FOR UPDATE AS
    DECLARE
    @CompanyId as varchar(20),
    @intUid as int
    SET @CompanyId =(Select vchrCompID from deleted)
    Set @intUid = (Select intUid from deleted)
    IF @CompanyId = 'sam'
    BEGIN
    UPDATE [dbPortal].[dbo].tblUser
    SET
    intEmp = (Select intEmp from inserted),
    vchrCompID = (Select vchrCompID from inserted),
    vchrPwd = (Select vchrPwd from inserted),
    vchrLogin = (Select vchrLogin from inserted),
    vchrFirstName = (Select vchrFirstName from inserted) ,
    vchrLastName = (Select vchrLastName from inserted),
    vchrEmailAddress = (Select vchrEmailAddress from inserted),
    bitActive = (Select bitActive from inserted),
    intPWDAttempt = (Select intPWDAttempt from inserted),
    vchrCreatedWho = (Select vchrCreatedWho from inserted),
    dtmCreatedDate = (Select dtmCreatedDate from inserted),
    vchrModifiedWho = (Select vchrModifiedWho from inserted),
    dtmModifiedDate = (Select dtmModifiedDate from inserted)
    WHERE
    intUID = @intUid
    END


    I get the error.
    What can I do to get this to run?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The "Inserted" virtual table contains one row for each record that was inserted during the transaction. Remember, triggers only execute once, no matter how many records are involved. So when one of your subqueries (such as "(Select vchrPwd from inserted)") returns more than one record, you get the error because SQL Server can't logically assign multiple values to a single parameter.

    Treat the [Inserted] and [Deleted] tables like any other tables. What you want is something like this:

    CREATE TRIGGER UPDATE_tblUser
    ON tblUser
    FOR UPDATE AS
    Update [dbPortal].[dbo].tblUser
    set intEmp = inserted.IntEmp,
    vchrCompID = inserted.vchrCompID,
    vchrPwd = inserted.vchrPwd,
    vchrLogin = inserted.vchrLogin,
    vchrFirstName = inserted.vchrFirstName,
    vchrLastName = inserted.vchrLastName,
    vchrEmailAddress = inserted.vchrEmailAddress,
    bitActive = inserted.bitActive,
    intPWDAttempt = inserted.intPWDAttempt,
    vchrCreatedWho = inserted.vchrCreatedWho,
    dtmCreatedDate = inserted.dtmCreatedDate,
    vchrModifiedWho = inserted.vchrModifiedWho,
    dtmModifiedDate = inserted.dtmModifiedDate
    from [dbPortal].[dbo].tblUser
    inner join inserted on intUID = inserted.intUID
    where inserted.vchrCompID = 'sam'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    message: Subquery returned more than 1 value. ...

    The Trigger is apparently not the problem since it is successfully used by other apps. I am now looking at the SQL statement that causes the Trigger to launch. By the way the application that uses the Trigger updates only one Row whereas the SQL statement is updating multiple rows.

    update dbo.tblUser
    set vchrPwd = dbo.fencrypt((SELECT charPassword + rtrim(dw.chrDeptNumber)
    FROM dbo.tblPasswords
    WHERE (dbo.tblPasswords.intIndex = dbo.tblUser.intUID)),getdate())
    FROM dbo.tblUser
    JOIN
    dbdw1.dbo.tblEmployee dw ON dw.intEmployeeNumber =
    dbo.tblUser.intEmp
    JOIN
    dbo.tblAppSecurity ON dbo.tblUser.intUID = dbo.tblAppSecurity.intUID
    WHERE ((dbo.tblAppSecurity.chrApplicationID = 'newapp') AND (dbo.tblUser.vchrCreatedWho = 'newuser')
    and vchrCompID = 'newcompany')

    This causes the Returned more that one value.

  4. #4
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    A simpler SQL Statement causing the same error

    UPDATE tblUser
    SET tblUser.vchrFirstName = dbo.TempEmployee.FirstName,
    tblUser.vchrLastName = dbo.TempEmployee.LastName,
    tblUser.vchrLogin = dbo.TempEmployee.LoginName,
    tblUser.bitActive = dbo.TempEmployee.Active,
    tblUser.vchrEmailAddress = dbo.TempEmployee.EmailAddress
    FROM dbo.TempEmployee, tblUser where dbo.TempEmployee.EmployeeID = tblUser.intEmp

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The trigger likely IS the problem. It fails whenever more than one record is updated because it is not written correctly. The fact that your applications only update one row at a time does not let your trigger off the hook.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Trigger Problem

    The Trigger errors out on the Set @intUid = (Select intUid from Inserted) as shown by the output from tne error text.

    CREATE TRIGGER UPDATE_tblUser
    ON dbo.tblUser
    FOR UPDATE AS
    DECLARE
    @intUid as int
    Set @intUid = (Select intUid from Inserted)

    Server: Msg 512, Level 16, State 1, Procedure UPDATE_tblUser, Line 6
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    The statement has been terminated.

  7. #7
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    blindman

    I paced the code as you suggusted and got the message

    Error 208: 'Invalid Object name Inserted'. when I selected Check Syntax in Trigger Properties.

  8. #8
    Join Date
    Dec 2003
    Location
    Oceanside,CA
    Posts
    31

    Trigger problem

    Seems to be working now. At least not erroring out. Now I have to test.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "Set @intUid = (Select intUid from Inserted)" is going to error out when multiple records are inserted into your table.

    Please understand:

    1) The trigger only fires ONCE for each insert statement, no matter how many records are affected.

    2) The "inserted" table holds one row for each record that was inserted by the transaction.

    3) An unfiltered SELECT from the "inserted" table can return more than one value.

    4) You cannot assign more than one value to a standard variable.

    You need to rethink your strategy here.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I rewrote this in his other thread dealing with the same topic. My guess is that we haven't heard anymore either because it works and he's moved on to other issues, or he's still trying to figure out how the UPDATE replaces the existing code (and works!).

    -PatP

Posting Permissions

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