Trigger is updating multiples rows with the same value

user_10909040 0
Viewed 52

The trigger is updating multiple rows with the same value

Here a problem, I'm trying to update one or many rows in a table(B), but its updating the rows with the same value let's say

TABLE A

+------+-----+-----+
|ROWID |NAME |CODE |
+------+-----+-----+
| 123  |pepe |  1  |
| 456  |tito |  2  |
| 789  |gege |  3  |
-------------------

Current result:

+------+-----+-----+---------+
|ROWID |NAME |CODE | A_ROWID |
+------+-----+-----+---------+
| 321  |rolo |  1  |  123    |
| 654  |kit  |  2  |  123    |
| 987  |gu   |  3  |  123    |
-----------------------------

Expected result:

+------+-----+-----+---------+
|ROWID |NAME |CODE | A_ROWID |
+------+-----+-----+---------+
| 321  |rolo |  1  |  123    |
| 654  |kit  |  2  |  456    |
| 987  |gu   |  3  |  789    |
-----------------------------

This is the trigger, I will really appreciate any advice

ALTER TRIGGER [dbo].[TRIG_AfterInsert] 
ON [dbo].[B] 
AFTER INSERT 
AS 
BEGIN
    DECLARE @Code nvarchar(100);

    SELECT @Code = CODE FROM INSERTED;

    UPDATE B 
    SET B.A_ROWID = A.ROWID 
    FROM A 
    WHERE A.CODE = @Code;
END
triggers tsql sql-server sql
1 Answers

The trigger is called once per the statement, which means if you are updating one row it will be called once, if you are updating 1 million rows it will be called once again.

So, now when you know triggers core behavior you should refactor these lines below:

declare @Code nvarchar(100);
select @Code=CODE from INSERTED;

So, full trigger code will be:

CREATE OR ALTER TRIGGER dbo.TRIG_AfterInsert ON dbo.B AFTER INSERT AS 
BEGIN
    UPDATE B
    SET B.A_ROWID = A.ROWID 
    FROM B 
         JOIN INSERTED I ON I.ROWID = B.ROWID
         JOIN A ON A.CODE = I.CODE;
END

Just take a look there is inserted table directly in the join, and with this statement you will filter all the rows you need.

Where clause in your statement is converted in the JOIN.

user_111207740
Related