SQL update trigger unable to handle multiple entries

Question

I have a SQL MERGE script that updates a table where an update trigger exists. When the MERGE scripts comes with only one update to the table the trigger works fine. When the MERGE command comes with multiple updates to the table the trigger returns an error. Here is the trigger:

ALTER TRIGGER [dbo].[userupd] 
ON  [dbo].[users] 
AFTER UPDATE
AS 
BEGIN
        SET NOCOUNT ON;

DECLARE @navn varchar(255), @fornavn varchar(255), @efternavn varchar(255),@initialer varchar(255), @areagroups varchar(255)

SET @fornavn = (SELECT Fornavn FROM DELETED)
SET @efternavn = (SELECT Efternavn FROM DELETED)
SET @initialer = (SELECT Initialer FROM DELETED)
IF  @initialer IS NULL SET @initialer = 'Extern'
SET @navn = @fornavn + ' ' + @efternavn + ' (' + @initialer + ')'
SET @areagroups = (SELECT AddedAreaGroups FROM NOX.dbo.simscodesusers WHERE Username = @navn)
SELECT @areagroups OriginalString, RTRIM(LTRIM(@areagroups)) TrimmedValue
SET @areagroups = ' ' + @areagroups

INSERT  INTO NOX.dbo.SIMScodesAutoUpdate
    ( Action ,
      Username
    )
    SELECT  'DELETE' ,
            D.Fornavn + ' ' + D.Efternavn + ' (' + D.Initialer + ')'
    FROM    DELETED D;               

INSERT  INTO NOX.dbo.SIMScodesAutoUpdate
    ( Action ,
      Username ,
      NoxAutoCode ,
      NoxAutoCodePIN ,
      UserGroup ,
      Startdate ,
      EndDate ,
      AddedAreaGroups
    )
    SELECT  'ADD' ,
            I.Fornavn + ' ' + I.Efternavn + ' (' + I.Initialer + ')' ,
            I.Kortnummer ,
            I.PINkode ,
            I.Brugerniveau ,
            I.Startdato ,
            I.Slutdato,
            @areagroups
    FROM    INSERTED I

END

This is the error returned from the SQL job that contains the MERGE script:

Executed as user: CPCORP\SQDKRTV96service. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. [SQLSTATE 21000] (Error 512) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.

Can the trigger be edited to handle multiple values?

Thanks in advance.

Maybe. The first question is: what happens when someone is a member of many groups? — Nick.Mc
– Nick.Mc, Commented Apr 18, 2016 at 12:24

valex · Accepted Answer · 2016-04-18 12:32:31Z

4

In the second case ("When the MERGE command comes with multiple updates...") the DELETED table contains MANY rows. So you can't assign MULTI rows table to the ONE SCALAR variable. Here is the source of the error 'Subquery returned more than 1 value....':

SET @fornavn = (SELECT Fornavn FROM DELETED)

Microsoft: Create DML Triggers to Handle Multiple Rows of Data

edited Apr 18, 2016 at 12:32

answered Apr 18, 2016 at 12:25

valex

24.2k7 gold badges47 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL update trigger unable to handle multiple entries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related