T-SQL UPDATE trigger help

Question

Hi iam trying to make an update trigger in my database. But i get this error every time the triggers trigs.

Error MEssage: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(3rows)

and heres my trigger

ALTER TRIGGER [dbo].[x1pk_qp_update]
        ON [dbo].[x1pk] FOR UPDATE
AS
BEGIN TRY
DECLARE @UserId int
      , @PackareKod int
      , @PersSign varchar(10)

    SELECT @PackareKod = q_packarekod
         , @PersSign = q_perssign
      FROM INSERTED

IF @PersSign IS NOT NULL
BEGIN
    IF EXISTS (SELECT * FROM [QPMardskog].[dbo].[UserAccount] WHERE [Account] = @PackareKod)
    BEGIN
    SET @UserId = (SELECT [UserId]
                     FROM [QPMardskog].[dbo].[UserAccount]
                    WHERE [Account] = @PackareKod)

        UPDATE [QPMardskog].[dbo].[UserAccount]
           SET [Active] = 1
         WHERE [Account] = @PackareKod

        UPDATE [QPMardskog].[dbo].[User]
           SET [Active] = 1
         WHERE [Id] = @UserId


    END
END

END TRY

But i only update one row in the table how can it says 3 rows. Please advise.

Answer 1

Well, your statement here:

 SELECT @PackareKod = q_packarekod, @PersSign = q_perssign
 FROM INSERTED

seems to assume your UPDATE trigger will be called for each row in an update statement. That is not the case - the UPDATE trigger will be called only once, and the Inserted pseudo-table contains three rows.

So you need to change your logic to be able to deal with multiple entries in the Inserted table.

Answer 2

you need to process this as a SET, it should be just two UPDATE statements, no variables, no IFs, all necessary conditions are in the UPDATE join and WHERE logic:

ALTER TRIGGER [dbo].[x1pk_qp_update]
        ON [dbo].[x1pk] FOR UPDATE
AS
BEGIN TRY

    UPDATE u
        SET [Active] = 1
        FROM [QPMardskog].[dbo].[UserAccount] u
        INNER JOIN INSERTED                   i ON u.[Account]=i.q_packarekod
        WHERE i.q_perssign IS NOT NULL

    UPDATE u
        SET [Active] = 1
        FROM [QPMardskog].[dbo].[User]  u
        WHERE [Id] IN (SELECT [UserId]
                           FROM [QPMardskog].[dbo].[UserAccount]
                           WHERE [Account] IN (SELECT q_packarekod
                                                   FROM INSERTED 
                                                   WHERE q_perssign IS NOT NULL
                                              )
                      )

END TRY

If I translated the IF and join logic properly, this should process 1 and/or many rows of [dbo].[x1pk] that get updated at one time.