'SQL Server update trigger not firing on all changes

This is for a intranet app that's been running fine for years. Recently users passwords have been randomly changing so I created an update trigger and a history table to try and track it down.

The table is AspNetUsers and this is my trigger

ALTER TRIGGER [dbo].[AspNetUsersUpdate]
   ON  [dbo].[AspNetUsers]
   AFTER UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO AspNetUsersHistory ([Id], [FirstName], [LastName], [PasswordHash], [SecurityStamp],..., LastUpdated)
    SELECT *,getdate()
    FROM deleted

END

One uses has had to reset his password that last 2 mornings so I went to the history table to see what's up.

SELECT a.Id
    ,a.LastName
    ,a.PasswordHash NewPassword
    ,h.passwordHash OldPassword
    ,h.lastUpdated 
FROM AspNetUsersHistory h
    INNER JOIN AspNetUsers a on h.Id = a.Id
WHERE a.Id = 3028
ORDER BY lastupdated DESC

returns:

Id      LastName    NewPassword         OldPassword lastUpdated
3028    Martin      CorrectPassword     WrongXXX    2022-02-15 08:54:17.5400000 +00:00
3028    Martin      CorrectPassword     WrongYYY    2022-02-15 08:36:53.7930000 +00:00
3028    Martin      CorrectPassword     WrongQQQ    2022-02-14 10:22:44.9000000 +00:00
3028    Martin      CorrectPassword     WrongZZZ    2022-02-14 08:32:10.9930000 +00:00

I used aliases for the New/Old password to make it more readable here. Every time he resets his password it records it to my history table. But at some point on 2/15 is password changed from CorrectPassword to WrongXXX and that wasn't recorded in my history table. The same thing happened on 2/14 as well.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source