Bulk update of the same row column using trigger in SQL Server

I have this table structure:

CREATE TABLE JoiningDate 
(
    Id INT,
    DateofJoining DATETIME,
    STATUS VARCHAR(10),
    DaysCount INT
)

This table is filled with 1000 records with DaysCount as NULL value.

I have created a trigger as below for this table to update DaysCount. Whenever there is an update of DateOfJoining or Status columns, DaysCount should get calculated in the trigger and updated automatically.

This is the outline of the trigger:

CREATE TRIGGER [dbo].[trigger_JoiningDate] 
ON [dbo].[JoiningDate]
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @Id INT
    DECLARE @DateJoining DATETIME
    DECLARE @Result INT

    SELECT @Id = Id,
           @DateJoining = DateofJoining, 
    FROM INSERTED   

    SET @Result = [Formula To Calculate Days]

    UPDATE JoiningDate 
    SET DaysCount = @Result  
    WHERE Id = @Id
END

I'm able to see the count getting updated for single record. But for bulk updates it is updating only the first record.

With the help of stackoverflow, I have tried using instead of trigger, TRIGGER_NESTLEVEL(), etc options but still all other rest of the rows not getting updated.

Quicker help appreciated.

Solution

I would recommend to split this up into two separate triggers - one for INSERT, one for UPDATE. Makes it much simpler to work with the code.

Also: your trigger will be called once per statement - not once per row - so you cannot just select a value from the Inserted pseudo table like this (since that pseudo table will contain all 25 newly inserted rows - not just one):

SELECT 
    @Id = Id,
    @DateJoining = DateofJoining, 
FROM INSERTED

Which of the 25 inserted rows would you be looking at?? It's arbitrary and undetermined - plus you'll just look at one row and ignore all other 24 rows.....

So the INSERT trigger should look like this:

CREATE TRIGGER [dbo].[trigger_JoiningDate_Insert] 
ON [dbo].[JoiningDate]
AFTER INSERT
AS
BEGIN
    UPDATE jd
    SET DaysCount = [Formula To Calculate Days]
    FROM JoiningDate jd
    INNER JOIN Inserted i ON i.Id = jd.Id
END

Since you have no previous values, there's no need to check if one of those two columns was updated.

Your UPDATE trigger should look like this:

CREATE TRIGGER [dbo].[trigger_JoiningDate_Update] 
ON [dbo].[JoiningDate]
AFTER UPDATE
AS
BEGIN
    UPDATE jd
    SET DaysCount = DATEDIFF(DAY, jd.DateofJoining, SYSDATETIME())
    FROM JoiningDate jd
    INNER JOIN Inserted i ON i.Id = jd.Id
    INNER JOIN Deleted d ON d.Id = i.Id
    -- check whether "DateOfJoining" or "Status" have been updated
    WHERE
        i.DateOfJoining <> d.DateOfJoining 
        OR i.Status <> d.Status 
END