4

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

Improve this question
1
  • Thank you so much @marc_s for style edit, much better and clearer now. Commented Jun 2, 2018 at 8:36

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

excellent, I have managed to do some test and you are absolutely correct. The only thing I would add is that as someone posted above, it appears that Inserted table is scoped to the trigger and since by design I only insert one row (never more) per activity per user, it will contain only one row. However, having said that, your approach is indeed the most robuts one and therefore I shall adopt it. Thank you very much!
-1

You cannot refer table fields like dbo.Activity.User_ID. Please change you trigger code like this..

CREATE TRIGGER Trk_Test 
    ON [dbo].[Activity]
    FOR INSERT
AS
BEGIN
    DECLARE @Id INT = 0

    SELECT @ID = [User_Id] FROM inserted

    UPDATE Users 
    SET Last_Activity_TimeStamp = GETDATE()
    WHERE [User_Id] = @Id

END

Inserted and Deleted tables Inserted /Deleted are the virtual table which have scope only to the trigger. You can not refer outside the trigger. So no matter how many users are connected at a time, inserted and deleted tables never conflicts.

Improve this answer

4 Comments

Your trigger has MAJOR flaw in that you seem to assume it'll be called once per row - that is not the case. The trigger will fire once per statement, so if your INSERT statement that causes this trigger to fire inserts 25 rows, you'll get the trigger fired once, but then Inserted pseudo table will contain 25 rows. Which of those 25 rows will your code select here?? SELECT @ID = [User_Id] FROM inserted - it's non-deterministic, you'll get one arbitrary row and you will be ignoring all other rows. You need to rewrite your trigger to take this into account!
Yes.. you are right @marc_s.. but hear in the above question, as described, there is only one row will be inserted in single transaction...
Yes - now, the OP says he's only inserted on row - but that could change in the future. Do NOT write code that only works under certain circumstances - especially if writing the proper code is so simple (as shown in my own answer)
Ok.. Thanks @marc_s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.