CODEWITHSUNDEEP
t-sqltriggers
theSpyCry
theSpyCry

Reputation: 12283

T-SQL Update trigger with multiple rows

Consider a trigger after update on the table A. For every update the trigger should update all the records in table B. Then consider this query:

UPDATE A SET X = Y

Apparently there are many rows updated. After the update the trigger takes place. Now if the trigger would be using inserted table, and you would like to update the table B with every single row of the temporary table inserted, and in MSDN is not recommended to use cursors, how would you do that?

Thank you

Upvotes: 1

Views: 1538

Answers (2)

Oded
Oded

Reputation: 498972

I will assume that table A is related to table B via a key (have to assume, as you posted no details).

If that is the case, you can use either sub-queries or joins with inserted to select the rows that need changing on table B.

UPDATE tableB B
SET B.colx = someValue
WHERE B.id IN
(
    SELECT b_id 
    FROM INSERTED
)

Upvotes: 1

marc_s
marc_s

Reputation: 754398

I don't know what exactly you want to do in your update trigger, but you could e.g.

UPDATE dbo.B
SET someColumn = i.Anothervalue
FROM Inserted i
WHERE b.Criteria = i.Criteria

or something else - you need to tell us a bit more about what it is you want to do with table B! But it's definitely possible to update, insert into or other things, without using a cursor and handling multiple rows from the Inserted table.

Upvotes: 3

Related Questions