Update Trigger To Update Records In Another Table

I have on User_Table CREATE TABLE [dbo].[User_TB] ( [User_Id] [varchar](15) NOT NULL, [User_FullName] [varchar](50) NULL, [User_Address] [varchar](150) NULL, [Us

Solution 1:

You would need something like this - a set-based solution that takes into account that in an UPDATE statement, you might be updating multiple rows at once, and therefore your trigger also must deal with multiple rows in the Inserted and Deleted tables.

CREATE TRIGGER [dbo].[updateUserId] 
ON [dbo].[User_TB]
FOR UPDATE
AS 
    -- update the "Break" table - find the rows based on the *old* User_Id
    -- from the "Deleted" pseudo table, andset it to the *new* User_Id
    -- from the "Inserted" pseudo table
    SET User_Id = i.User_Id 
    FROM Inserted i
    INNER JOIN Deleted d ON i.TID = d.TID
    WHERE
        Break_TB.User_Id = d.User_Id

    -- update the "Log" table - find the rows based on the *old* User_Id
    -- from the "Deleted" pseudo table, andset it to the *new* User_Id
    -- from the "Inserted" pseudo table
    UPDATE Break_TB 
    SET User_Id = i.User_Id 
    FROM Inserted i
    INNER JOIN Deleted d ON i.TID = d.TID
    WHERE
        Break_TB.User_Id = d.User_Id

This code assumes that the TID column in the User_TB table is the primary key which remains the same during updates (so that I can join together the "old" values from the Deleted pseudo table with the "new" values after the update, stored in the Inserted pseudo table)