Help a Newbie? SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=

Transact-SQL
1

Hi Everyone, I did a little research but I am not sure how to fix this issue. I read what the cause is but I don't know how to fix it because I'm still new to SQL. I inherited a db (with custom trigger) and the trigger is causing an error "SQL Server Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >=". I read that the error is caused by a subquery returning more results than 1. Unfortunately I do not know what the subquery is :frowning:

I pasted the trigger below and I hope someone here is kind of enough to tell me what to change to fix this!

Thanks in advance.

Here is the trigger:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER

TRIGGER [db1].[WKFLWHistoryTrigger] ON [db1].[PROFILE]
FOR

INSERT, UPDATE
AS

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT ON;

DECLARE @intErrorCode INT, @ErrorDescrip varchar(50), @WKFLW_Datetime datetime, @Deleted int, @Inserted int

BEGIN TRAN;

SET @WKFLW_Datetime = getutcdate()

SET @Deleted = (SELECT wkflw_assign2 FROM Deleted)

SET @Inserted = (SELECT wkflw_assign2 FROM Inserted)

IF UPDATE(wkflw_assign2)

BEGIN

IF (@Deleted Is Null AND @Inserted = 0)

BEGIN

UPDATE db1.[Profile]

SET wkflw_assigndt = NULL

FROM inserted

WHERE db1.[Profile].SYSTEM_ID = inserted.SYSTEM_ID

END

ELSE

BEGIN

INSERT INTO dbo.WKFLWHistory (SYSTEM_ID, WKFLW_Value, WKFLW_Datetime)

SELECT SYSTEM_ID, wkflw_assign2 , @WKFLW_DateTime

FROM inserted

UPDATE db1.[Profile]

SET wkflw_assigndt = @WKFLW_Datetime

FROM inserted

WHERE db1.[Profile].SYSTEM_ID = inserted.SYSTEM_ID

END

END

IF @@ERROR <> 0

BEGIN

SELECT @intErrorCode = @@ERROR

SELECT @ErrorDescrip = 'Error recoding WKFLW_ASSIGN Time - '+ convert(varchar(10),@intErrorCode)

IF (@intErrorCode <> 0) GOTO ERRORHANDLER

END

COMMIT TRAN;

ERRORHANDLER:

IF (@intErrorCode <> 0) BEGIN

PRINT (IsNull(@ErrorDescrip, 'Unexpected error occurred'));

ROLLBACK TRAN;

END
END

2

Deleted and Inserted trigger tables can contain multiple records and this is probably what is happening in your case, more than one record got affected by the insert\update. The assignment to @Deleted@Inserted will work only for a single row. So you need to alter your logic a bit and join back with the Deleted and Inserted tables.

Please go through this to understand how these tables\trigger works:

3 
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [db1].[WKFLWHistoryTrigger] 
ON [db1].[PROFILE] 
AFTER 
INSERT, UPDATE 
AS

SET NOCOUNT ON;
SET XACT_ABORT ON;

IF UPDATE(wkflw_assign2)
BEGIN
    DECLARE @is_insert bit
    DECLARE @WKFLW_Datetime datetime
    SET @is_insert = CASE WHEN EXISTS(SELECT 1 FROM deleted) THEN 0 ELSE 1 END
    SET @WKFLW_Datetime = GETUTCDATE()
    
    IF @is_insert = 1
        UPDATE p
        SET wkflw_assigndt = NULL
        FROM db1.[Profile] p
        INNER JOIN inserted i ON p.SYSTEM_ID = i.SYSTEM_ID
        WHERE i.wkflw_assign2 = 0

    INSERT INTO dbo.WKFLWHistory (SYSTEM_ID, WKFLW_Value, WKFLW_Datetime)
    SELECT i.SYSTEM_ID, i.wkflw_assign2 , @WKFLW_Datetime
    FROM inserted i
    WHERE @is_insert = 0 OR (i.wkflw_assign2 IS NULL OR i.wkflw_assign2 <> 0)

    UPDATE p
    SET wkflw_assigndt = @WKFLW_Datetime
    FROM db1.[Profile] p
    INNER JOIN inserted i ON p.SYSTEM_ID = i.SYSTEM_ID
    WHERE @is_insert = 0 OR (i.wkflw_assign2 IS NULL OR i.wkflw_assign2 <> 0)
END /*IF*/