CODEWITHSUNDEEP
sql-server
Derek Jee
Derek Jee

Reputation: 147

Subquery returned more than 1 value when trigger executes

I have a trigger which adds a log entry into a table upon a field change in another table. it works when one row is changed but errors when multiple rows re changed. Anyone out there able to explain what I have to do to get my trigger working also for multi row updates?

Many thanks,

Derek

Declare @PropertyID uniqueidentifier

    Set @PropertyID = (Select CONVERT(VARCHAR( 36 ), ISNULL(i.[PropertyPK], d.[PropertyPK]))
    FROM 
        INSERTED i 
    FULL OUTER JOIN DELETED d ON ( d.[PropertyPK] = i.[PropertyPK] )

    WHERE 
        ( d.[strManagingOfficeName] <> i.[strManagingOfficeName] ) OR
        ( d.[strManagingOfficeName] IS NULL AND i.[strManagingOfficeName] IS NOT NULL ) OR
        ( i.[strManagingOfficeName] IS NULL AND d.[strManagingOfficeName] IS NOT NULL ))

    Declare @CompanyID uniqueidentifier

    Set @CompanyID = (Select CompanyFK From Property Where PropertyPK = @PropertyID)

    --Deleted Old Ones

    Delete From TDSAPILog Where ObjectFK = @PropertyID And strObject = 'Branch Change'

    --Insert New Log

    INSERT dbo.TDSAPILog(TDSAPILogPK, ObjectFK, strObject, strStatus, CompanyFK, dteDateLogged)
    SELECT
        NewID(),
        @PropertyID,
        'Branch Change',
        'Active',
        @CompanyID ,
        GetDate()

Upvotes: 0

Views: 156

Answers (1)

Faraz Babakhel
Faraz Babakhel

Reputation: 664

This error occur when you return more than 1 value from a query and save in a variable or compare with a value in where clause.

In your example I think the error occur at this line

SET @CompanyID = (SELECT CompanyFK FROM Property WHERE PropertyPK = @PropertyID)

To resolve the reported error just put "TOP 1" in your query. Example is shown here:

SET @CompanyID = (SELECT TOP 1 CompanyFK FROM Property WHERE PropertyPK = @PropertyID)

Subquery returned more than 1 value error may occur at the following scenarios:

SET @YouVariable = (SELECT ColumnID FROM yourTable WHERE Identity = @SomeValue)

-- if the above query return more than 1 value the same error will be occurred
-- to resolve this problem just put "TOP 1" before ColumnID  

SELECT * 
FROM OtherTable 
WHERE OtherIdentity = ((SELECT ColumnID FROM yourTable 
                        WHERE Identity = @SomeValue))

-- if the above query return more than 1 value the same error will be occurred
-- to resolve this problem just replace "= with IN()". Example give below
SELECT * 
FROM OtherTable 
WHERE OtherIdentity IN ((SELECT ColumnID FROM yourTable 
                         WHERE Identity = @SomeValue))

Upvotes: 1

Related Questions