'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 |
|---|
