Update multiple rows by the trigger in SQL Server

Question

I use below trigger for updating rows, if fld_IsUpdated set 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;

When I execute an update query like UPDATE tbl_GroupChat SET fld_IsUpdated = 1;, SSMS shows below error:

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.

I test many structures of triggers but all of them has the same error, for example with below code, I don't declare variable:

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

George Menoutis · Accepted Answer · 2019-03-12 10:46:45Z

1

In here:

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

you compare fld_IsUpdated to 1. However, the inserted table has multiple rows, and thus the select is an entire table. A table cannot be compared to a single value.

Try this:

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;

edited Mar 12, 2019 at 10:46

answered Mar 12, 2019 at 10:42

George Menoutis

7,3104 gold badges23 silver badges51 bronze badges

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

1 Comment

Hajitsu Over a year ago

How to check if the fld_IsUpdated update to true or not? because I want to fire the trigger if field set to true.

Collectives™ on Stack Overflow

Update multiple rows by the trigger in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related