SQL Server 2008 ::子查询返回多个值。这不是当子查询跟随允许=，！=，<, <= , >，> =

Question

我[ReconcileUserInformationComputed]上Userinformation表

Userinformation表具有下面的行 [ID]触发，[CompanyID]，[状态]， [名字]，[姓氏]

UserinformationComputed表有以下行 [ID]，[CompanyID]，[law_id]，[状态]，[名字]，[姓氏]

下面是我的触发

USE [einvoice] 
GO 
/****** Object: Trigger [dbo].[ReconcileUserInformationComputed] Script Date: 08/27/2014 10:53:08 ******/ 
SET ANSI_NULLS ON 
GO 
SET QUOTED_IDENTIFIER ON 
GO 



ALTER TRIGGER [dbo].[ReconcileUserInformationComputed] ON [dbo].[UserInformation] AFTER INSERT,DELETE,UPDATE 
AS 
    IF @@ROWCOUNT = 0 -- exit trigger when zero records affected 
    BEGIN 
     RETURN 
    END 
    SET NOCOUNT ON; 
    IF EXISTS (SELECT * FROM INSERTED) 
    BEGIN 
     IF EXISTS (SELECT * FROM DELETED) 
     BEGIN 
      --UPDATE 
       UPDATE [dbo].[UserInformationComputed] 
       SET -- use new values from inserted 
       CompanyID = (SELECT CompanyID from inserted), 
       law_id = (SELECT ID FROM inserted), 
       Status = (SELECT Status FROM inserted), 
       FirstName = (SELECT FirstName FROM inserted), 
       LastName = (SELECT LastName FROM inserted), 
    WHERE -- use original values from deleted 
       law_id = (SELECT ID FROM deleted) 
    END 
    ELSE 
    BEGIN 
     --INSERT 
     INSERT INTO [dbo].[UserInformationComputed] (CompanyID,law_id,Status,FirstName,LastName) 
    SELECT CompanyID,id,Status,FirstName,LastName) FROM inserted 
    END 
END 
ELSE IF EXISTS(SELECT * FROM DELETED) 
BEGIN 
    --DELETE 
    DELETE FROM [dbo].[UserInformationComputed] 
    WHERE law_id = (SELECT id FROM deleted) 
END 

时尝试更新Userinformation多个用户正在以下错误越来越

Msg 512, Level 16, State 1, Procedure ReconcileUserInformationComputed, Line 16 
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. 

我所做的更改按照它的工作对上述触发，但同样的更改没有工作的另一个触发

ALTER TRIGGER [dbo].[ReconcileCrossRefComputed] ON [dbo].[CrossRef] AFTER INSERT, UPDATE, DELETE 
AS 
    IF @@ROWCOUNT = 0 -- exit trigger when zero records affected 
    BEGIN 
     RETURN 
    END 
    SET NOCOUNT ON; 
    IF EXISTS (SELECT * FROM INSERTED) 
    BEGIN 
     IF EXISTS (SELECT * FROM DELETED) 
     BEGIN 
      --UPDATE 
      UPDATE [dbo].[CrossRefComputed] 
      SET -- use new values from inserted 
      SenderId = inserted.SenderId, 
      ReceiverId = inserted.ReceiverId, 
      ForeignRef = inserted.ForeignRef, 
      PolicyID = inserted.PolicyID, 
     From inserted 
     WHERE -- use original values from deleted 
     [CrossRefComputed].SenderId = inserted.SenderId 
     AND [CrossRefComputed].ReceiverId = inserted.ReceiverId 
    END 
    ELSE 
    BEGIN 
     --INSERT 
     INSERT INTO [dbo].[CrossRefComputed] (SenderId, ReceiverId, ForeignRef, PolicyID) 
     SELECT SenderId, ReceiverId, Effective, PolicyID FROM inserted 
    END 
END 
ELSE IF EXISTS(SELECT * FROM DELETED) 
BEGIN 
    --DELETE 
    DELETE FROM [dbo].[CrossRefComputed] 
    WHERE SenderId in (SELECT SenderId FROM deleted) 
    AND ReceiverId in (SELECT ReceiverId FROM deleted) 
END 

答案

任何人请帮助我如何修复程序来处理更新多个记录？

Answer 1

要知道，一个UPDATE或DELETE可以影响一个以上的记录，您的触发将有一次对付他们，在这种情况下，你的“插入”或“删除”表将有一个以上的记录。

你将不得不使用你的DML联接。像这样的：

UPDATE [dbo].[UserInformationComputed] 
       SET 
       CompanyID = inserted.CompanyID, 
       law_id = inserted.ID, 
       Status = inserted.Status, 
       FirstName = inserted.FirstName, 
       LastName = inserted.LastName 
      from inserted 
    WHERE 
       UserInformationComputed.law_id = inserted.ID 

我还没有完全理解你的更新逻辑，所以你将不得不适应我的代码。

在你的DELETE命令，你可能刚刚改变 “=” 到 “中”：

DELETE FROM [dbo].[UserInformationComputed] 
    WHERE law_id in (SELECT id FROM deleted) 

看看这个：

http://msdn.microsoft.com/en-us/library/ms190752.aspx