通过Server中的触发器更新多行

Question

如果fld_IsUpdated设置为true，我将在下面使用触发器来更新行。

ALTER TRIGGER [dbo].[UpdateFieldDate_GroupChat] ON [dbo].[tbl_GroupChat]
AFTER UPDATE
AS
     SET NOCOUNT ON;
     IF
     (
         SELECT i.fld_IsUpdated
         FROM inserted i
     ) = 1
         BEGIN
             DECLARE @now BIGINT= (CONVERT([BIGINT], replace(replace(replace(CONVERT([VARCHAR](19), GETDATE(), (121)), ':', ''), '-', ''), ' ', ''), (0)));
             UPDATE tbl_GroupChat
               SET 
                   tbl_GroupChat.fld_ModifiedAt = @now
             WHERE tbl_GroupChat.fld_Id =
             (
                 SELECT i.fld_Id
                 FROM inserted i
             );
     END;

当我执行像UPDATE tbl_GroupChat SET fld_IsUpdated = 1;这样的更新查询时，SSMS显示了下面的错误：

Msg 512, Level 16, State 1, Procedure UpdateFieldDate_GroupChat, Line 5 [Batch Start Line 0]
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

我测试了许多触发器的结构，但它们都有相同的错误，例如，对于下面的代码，我不声明变量：

 tbl_GroupChat.fld_ModifiedAt = (CONVERT([BIGINT], replace(replace(replace(CONVERT([VARCHAR](19), GETDATE(), (121)), ':', ''), '-', ''), ' ', ''), (0)))

Answer 1

在这里：

IF
     (
         SELECT i.fld_IsUpdated
         FROM inserted i
     ) = 1

但是，fld_IsUpdated表有多个行，因此select是一个完整的表。表不能与单个值相比较。

试试这个：

ALTER TRIGGER [dbo].[UpdateFieldDate_GroupChat] ON [dbo].[tbl_GroupChat]
AFTER UPDATE
AS
    SET NOCOUNT ON;

    BEGIN
        DECLARE @now BIGINT= (CONVERT([BIGINT], replace(replace(replace(CONVERT([VARCHAR](19), GETDATE(), (121)), ':', ''), '-', ''), ' ', ''), (0)));

        UPDATE t 
        SET tbl_GroupChat.fld_ModifiedAt = @now
        FROM tbl_GroupChat as t
        INNER JOIN inserted i ON t.fld_Id=i.fld_Id
        WHERE i.fld_IsUpdated=1
     END;