Use SQL Server trigger to update a column in another table after an insert

Question

I am trying to accomplish the following using a SQL Server trigger: once a new row is inserted into a table, I would like to update a column in another table.

For example: I have a table called Users and a table called Activity. Every time a client connects to the server, a row is inserted into the Activity table; once the row is inserted (which contains user_id), I would like to update the column in the Users table that stores the date and time of the last activity.

UPDATE Users
SET Last_Activity_TimeStamp = GETDATE()
WHERE Users.User_ID = Activity.User_ID

But SSMS throws an error saying:

The multi-part identifier "dbo.Activity.User_ID" could not be bound

If I change the above update statement to:

UPDATE Users
SET Last_Activity_TimeStamp = GETDATE()
WHERE Users.User_ID = User_ID

All the rows in Users table are updated.

I understand that there is an Inserted table and that the rows are added there before they are inserted in to the main Activity table, however I have potentially many users connecting up and there will be many rows inserted in to Activity table. Potentially a 1000 per second. I would like to know how to get the user_id of the row being inserted in to Activity table and update the Last_Activity_TimeStamp in the Users table.

Edit: I guess where I am lacking the knowledge is that I do not know how many rows can be in an Inserted table at any time, given that in my design a single transaction only inserts a single row. Is it possible that there may be more than one row in the Inserted table from other active transactions since there are multiple users connecting and disconnecting?

Any help will be greatly appreciated!

Thank you

Answer 1

The other responses both fail to take into account that the Inserted pseudo table in a trigger will contain MULTIPLE rows at times - one should never ever select a single value - this will NOT WORK as expected if multiple rows are being inserted at once.

The trigger must be written in a set-based manner to handle this situation - which is really quite easy - try something like this:

CREATE TRIGGER trgActivityInsert
ON [dbo].[Activity]
FOR INSERT
AS
BEGIN
    UPDATE u
    SET Last_Activity_TimeStamp = GETDATE()
    FROM dbo.Users u
    INNER JOIN Inserted i ON u.User_Id = i.User_Id
END

With this, your trigger will work just fine whether you insert one, ten or a hundred rows at once, and all corresponding entries in the dbo.User table will be updated properly.