CODEWITHSUNDEEP
sqlsql-servert-sql
Coloplast
Coloplast

Reputation: 95

SQL update trigger unable to handle multiple entries

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.

Upvotes: 2

Views: 87

Answers (1)

valex
valex

Reputation: 24144

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

Upvotes: 4

Related Questions