SQL update触发器不能处理多个条目

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:

我有一个SQL合并脚本，它更新存在更新触发器的表。当合并脚本只有一个更新到表时，触发器工作正常。当MERGE命令对表进行多次更新时，触发器将返回一个错误。触发器:

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:

这是包含合并脚本的SQL作业返回的错误:

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.

作为用户:执行CPCORP \ SQDKRTV96service。子查询返回超过一个值。当子查询跟随=、!=、<、<=、>、>=或当子查询用作表达式时，这是不允许的。[SQLSTATE 21000](错误512)语句已被终止。(SQLSTATE 01000)(错误3621)。这一步失败了。

Can the trigger be edited to handle multiple values?

可以编辑触发器来处理多个值吗?

Thanks in advance.

提前谢谢。

1 个解决方案

#1

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....':

在第二个例子中(“当合并命令有多个更新时……”)被删除的表包含许多行。所以你不能将多行表分配给一个标量变量。这是错误的来源“....子查询返回超过1值”:

SET @fornavn = (SELECT Fornavn FROM DELETED)

Microsoft: Create DML Triggers to Handle Multiple Rows of Data

创建DML触发器来处理多行数据